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)
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