VBA - Dynamic multi column list with checkboxes for printing labels (through a DYMO printer)

Question

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 ^^,) :

Problem :

I actually have my lists under this form :

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)

The result should look like this :

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

        for each content
            if checkbox checked
                write content in the 2nd position of the dataarray
                write Nb in 3rd position of the dataarray

                for each CarModel
                    if checkbox checked
                       write car model in the 4th position of the dataarray

                        For i = 1 To Content.Value
                            For Each data In datarray
                                print that in the required column in the print sheet
                            Next
                        Next
                    End If
                Next
            End If
        Next
    End If
Next

Which gives :

For Each Dealer In Worksheets(MenuSheet).Range(PartnerListPos & FirstLine + 1 & ":" & PartnerListPos & PartnerListEnd)    'for each dealer
    If Worksheets(MenuSheet).Range(Dealer.Address).Offset(0, 1).Value = True Then 'if the corresponding checkbox is checked
        'to encode the data, if requested
        Set EncodeDealer = Worksheets(MenuSheet).Range(EncodeInfoPos & FirstLine + 1 & ":" & EncodeInfoPos & EncodeInfoEnd).Find("Bugatti Dealers")
        If EncodeDealer.Offset(0, 1) = True Then
            FinalData(1) = EncodeDecode.Base64EncodeString(Dealer)
        Else
            FinalData(1) = Dealer
        End If


        For Each Content In Worksheets(MenuSheet).Range(ContentContentPos & FirstLine + 1 & ":" & ContentContentPos & ContentContentEnd)      'for each Content
            If Worksheets(MenuSheet).Range(Content.Address).Offset(0, 2).Value = True Then 'if the corresponding checkbox is checked
                'to encode the data, if requested
                Set EncodeContentContent = Worksheets(MenuSheet).Range(EncodeInfoPos & FirstLine + 1 & ":" & EncodeInfoPos & EncodeInfoEnd).Find("Contents Nb.")
                If EncodeDealer.Offset(0, 1) = True Then
                    FinalData(2) = EncodeDecode.Base64EncodeString(Worksheets(MenuSheet).Range(Content.Address).Offset(0, 1).Value)
                Else
                    FinalData(2) = Worksheets(MenuSheet).Range(Content.Address).Offset(0, 1).Value
                End If


                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
                        'to encode the data, if requested
                        Set EncodeCar = Worksheets(MenuSheet).Range(EncodeInfoPos & FirstLine + 1 & ":" & EncodeInfoPos & EncodeInfoEnd).Find("Cars Models")
                        If EncodeCar.Offset(0, 1) = True Then
                            FinalData(3) = EncodeDecode.Base64EncodeString(CarModel)
                        Else
                            FinalData(3) = CarModel
                        End If

                        'writing down the data
                        For NbExec = 1 To Worksheets(MenuSheet).Range(NbLabelPos & Content.Row).Value
                            For Each data In FinalData

                                Worksheets(PrintSheet).Range(ColExit & LineExit + FirstLineData).Value = data
                                ColExit = Split(Cells(1, Range(ColExit & 1).Column + 1).Address, "$")(1)
                            Next
                            If ColExit = Split(Cells(1, 1 + UBound(FinalData)).Address, "$")(1) And NbExec < Worksheets(MenuSheet).Range(NbLabelPos & Content.Row).Value Then
                                ColExit = "A"
                                LineExit = LineExit + 1
                            End If
                        Next
                        LineExit = LineExit + 1
                        ColExit = "A"
                    End If
                Next
            End If
        Next
    End If
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 !

Does a way to "not start a loop if a variable is false, but execute once the content" exists? I could use the first code I wrote, and state that "if there is no checkbox checked in the column, don't start the loop, but execute what's in it once"... A while... Do? — zepeu, Dec 10 '19 at 12:17
in VBA there are 3 types of `Do` loops. 1) Do While 2) Do Until 3)Do. The first two require some kind of condition that must be met. The third will loop endlessly until something inside the loop calls an `Exit Loop` — Glenn G, Dec 10 '19 at 13:07
@GlennG I know about these loops, but they can't help me... In the case the first loop won't be executed (because the exit factor is immadiately met), all the rest won't be executed... — zepeu, Dec 10 '19 at 13:33
have you tried a `For...Each` loop on the cells? cycle through each of the cells in the range and check to see if the condition is met then do something if it is — Glenn G, Dec 10 '19 at 13:36
@GlennG it's exactly what I do, and if the first IF don't find any cell with "true" in it, nothing of the rest is executed. By the way, the problem comes from the IFs statements and not the "for" loops, I'll edit my post... — zepeu, Dec 10 '19 at 13:55
I guess I'll be obliged to use the if statements and count the number of checked checkboxes before starting the looping... and there is the case when the first column has nothing checked, then the second has nothing checked etc etc... I'll have a lot of redundant code :( I'm pretty sure it's not doable... if I start to have 5, 6, 10 columns, it will be a 10 000 lines code :'( — zepeu, Dec 10 '19 at 14:04
If the user is checking the boxes, add a checkbox to the top of each column that will work as a flag that that column is to be skipped in the code. — Glenn G, Dec 10 '19 at 14:06
@GlennG I already have this checkbox (the one with "print?"), but I want to fill my arrays, then choose to copy or not the data. And, anyway, there is still the possibility not to have any checkbox checked... — zepeu, Dec 10 '19 at 14:25

score 0 · Accepted Answer · answered Dec 11 '19 at 10:33

Ok, I think I got a solution, dirty one, but working... I used the "GoTo Label" function and variables counting the number of checkboxes checked this way :

if NbDealer = 0 then
    GoTo NoDealer
End if
for each dealer
    if checkbox checked
        Write dealer in the 1st position of the dataarray
NoDealer :
        if NbContent = 0 then
            GoTo NoContent
        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
NoContent:
                if NbCars = 0 then
                    GoTo NoCars
                End if
                for each CarModel
                    if checkbox checked
                       write car model in the 4th position of the dataarray
NoCars:
                        For Each data In datarray
                            print that in the required column in the print sheet
                        Next

                    End If
                if NbCars = 0 then    'just to avoid passing on the "Next" of the non initialized For loop
                    GoTo EndCars
                End if
                Next
EndCars:
            End If
        if NbContent = 0 then
            GoTo EndContent
        End if
        Next
EndContent:
    End If
if NbDealer = 0 then
    GoTo EndDealer
End if
Next
EndDealers:

VBA - Dynamic multi column list with checkboxes for printing labels (through a DYMO printer)

1 Answers1