mardi 10 décembre 2019

Dynamic multi column list with checkboxes for printing on a label (through a DYMO printer)

Context :

I work for a cars manufacturer firm, and I need to create an excel table permitting to generate a list which will be printed by a label printer of the brand DYMO.

These labels will have several data in a QR code.

The DYMO software can read an excel table, then print a bunch of labels.

It reads the excel table in a "line by line" manner, each line = 1 label printed, each column is a different data that can be integrated where we decide to.

here is the interface (yep, I'm french ^^,) : DYMO print menu

Problem :

I actually have my lists under this form : Original data table

The idea is that a user can check the checkboxes to choose the data which will be in the labels, then generate a list through the green button "Create print sheet"

The top checkbox of every column, called "Print?" is to allow the user to generate labels for dealers (as example) but without having the dealer information on it (or content or other)

I coded it with "for loops" using an array in a way like that :

for each dealer
    if checkbox checked 
        Write dealer in the 1st position of the dataarray
    end if
    for each content
        if checkbox checked
            write content in the 2nd position of the dataarray
            write Nb in 3rd position of the dataarray
        end if
        for each CarModel
            if checkbox checked
               write car model in the 4th position of the dataarray
            end if
            for i = 1 to content.value
                for each data in datarray
                    print that in the required column in the print sheet
                next
            next
        next
    next
next

The big problem with that is that when someone wants to print a label with no dealer, but just the content, the first "if statement" blocks everything, so, there is nothing to print...

I've started to code it in another way, through some "select case", but we will probably add some columns to this file, and with only these 3 data (Content + Nb are together) I already have 8 cases... I guess you know how fast this can go if we add a few columns. That's not possible to do.

*I have no idea what kind of solution would exist to answer my problem? I don't even know what to write on a search engine to try to have an answer :/ *

Here is the select case code (not finished as it's useless to continue) :

Select Case DealerChkBx 'Dealer
    Case Is = 0 'Dealer
        Select Case FTINbChkBx    'FTI
            Case Is = 0    'FTI
                Select Case CarsChkBx   'Cars
                    Case Is = 0   'Cars 0 0 0
                        pouet = MsgBox("At least one checkbox should be checked...", vbOKOnly, "Nothing...")
                    Case Is > 0   'Cars 0 0 1
                        For Each CarModel In Worksheets(MenuSheet).Range(CarsModelsPos & FirstLine + 1 & ":" & CarsModelsPos & CarsModelsEnd)      'for each car
                            If Worksheets(MenuSheet).Range(CarModel.Address).Offset(0, 1).Value = True Then 'if the corresponding checkbox is checked
                                If EncodeCar.Offset(0, 1) = True Then
                                    OneMoreCar = OneMoreCar + 1
                                    ReDim Preserve FinalData(LBound(FinalData) To UBound(FinalData), 1 To CarsChkBx)
                                    FinalData(4, OneMoreCar) = EncodeDecode.Base64EncodeString(CarModel)
                                Else
                                    OneMoreCar = OneMoreCar + 1
                                    ReDim Preserve FinalData(LBound(FinalData) To UBound(FinalData), 1 To OneMoreCar)
                                    FinalData(4, OneMoreCar) = CarModel
                                End If
                            End If
                        Next
                    End Select

            Case Is > 0    'FTI
                Select Case CarsChkBx   'Cars
                    Case Is = 0   'Cars 0 1 0
                        For Each FTINb In Worksheets(MenuSheet).Range(FTINbPos & FirstLine + 1 & ":" & FTINbPos & FTIContentEnd)      'for each car
                            If Worksheets(MenuSheet).Range(FTINb.Address).Offset(0, 1).Value = True Then 'if the corresponding checkbox is checked
                        'If Worksheets(MenuSheet).Range(CarsModelsPos & FirstLine).Value = True Then 'if it is to be printed
                                If EncodeCar.Offset(0, 1) = True Then
                                    OneMoreFTI = OneMoreFTI + 1
                                    ReDim Preserve FinalData(LBound(FinalData) To UBound(FinalData), 1 To FTINbChkBx)
                                    FinalData(2, OneMoreFTI) = EncodeDecode.Base64EncodeString(FTINb.Offset(0, -1).Value)
                                    FinalData(3, OneMoreFTI) = EncodeDecode.Base64EncodeString(FTINb)
                                Else
                                    OneMoreFTI = OneMoreFTI + 1
                                    ReDim Preserve FinalData(LBound(FinalData) To UBound(FinalData), 1 To FTINbChkBx)
                                    FinalData(2, OneMoreFTI) = FTINb.Offset(0, -1).Value
                                    FinalData(3, OneMoreFTI) = FTINb
                                End If
                            End If
                        Next
                    Case Is > 0   'Cars 0 1 1

                End Select

        End Select

    Case Is > 0 'Dealer
        Select Case FTINbChkBx    'FTI
            Case Is = 0    'FTI
                Select Case CarsChkBx   'Cars
                    Case Is = 0   'Cars 1 0 0
                        For Each Dealer In Worksheets(MenuSheet).Range(DealerPos & FirstLine + 1 & ":" & DealerPos & DealerEnd)      'for each car
                            If Worksheets(MenuSheet).Range(Dealer.Address).Offset(0, 1).Value = True Then 'if the corresponding checkbox is checked
                        'If Worksheets(MenuSheet).Range(CarsModelsPos & FirstLine).Value = True Then 'if it is to be printed
                                If EncodeDealer.Offset(0, 1) = True Then
                                    OneMoreDealer = OneMoreDealer + 1
                                    ReDim Preserve FinalData(LBound(FinalData) To UBound(FinalData), 1 To DealerChkBx)
                                    FinalData(1, OneMoreDealer) = EncodeDecode.Base64EncodeString(Dealer)
                                Else
                                    OneMoreDealer = OneMoreDealer + 1
                                    ReDim Preserve FinalData(LBound(FinalData) To UBound(FinalData), 1 To DealerChkBx)
                                    FinalData(1, OneMoreDealer) = Dealer
                                End If
                            End If
                        Next
                    Case Is > 0   'Cars 1 0 1

                End Select
            Case Is > 0    'FTI
                Select Case CarsChkBx   'Cars
                    Case Is = 0   'Cars 1 1 0

                    Case Is > 0   'Cars 1 1 1

                End Select
        End Select
End Select 

Hoping my request is understandable, thank you all by advance !




Aucun commentaire:

Enregistrer un commentaire