excel - Vba code to print multiple a4 size label with barcode - Stack Overflow

admin管理员组

文章数量:1316376

I am new to macro could you please help me correct where I am going wrong with this code. I can print if the value of last 2 digit is numeric but cant print if the value is non numeric.

I want to print the barcode labels

For Example : if my input is like DA-01-02 and DA-01-10. I can generate label and print but if the value is like DA-01-AA and DA-01-BA it does not print? can anyone help me on this

thank you

Private Sub CommandButton1_Click()

a = MsgBox("Are you sure you want to print labels?", vbYesNo + vbQuestion, "Attention!")
If a = 7 Then GoTo 10


Set start1 = ActiveSheet.Cells(7, 112)
Set start2 = ActiveSheet.Cells(7, 113)
Set start3 = ActiveSheet.Cells(7, 114)
Set start4 = ActiveSheet.Cells(7, 115)
Set end1 = ActiveSheet.Cells(10, 112)
Set end2 = ActiveSheet.Cells(10, 113)
Set end3 = ActiveSheet.Cells(10, 114)
Set end4 = ActiveSheet.Cells(10, 115)
 
tog = 0
For r = 2 To 38
    If Mid(ActiveSheet.Cells(5, 114), 1, 1) = ActiveSheet.Cells(20, r) Then tog = 1
Next r
    If tog = 0 Then a = MsgBox("Invalid Aisle", vbCritical, "Error!")
    If tog = 0 Then GoTo 10

tog = 0
For s = 2 To 38
    If Right(ActiveSheet.Cells(5, 114), 1) = ActiveSheet.Cells(20, s) Then tog = 1
Next s
    If tog = 0 Then a = MsgBox("Invalid Aisle", vbCritical, "Error!")
    If tog = 0 Then GoTo 10
 
For r = 112 To 115
    tog = 0
    
    
    If ActiveSheet.Cells(7, r).Value = 0 Or ActiveSheet.Cells(7, r) = 1 Or ActiveSheet.Cells(7, r) = 2 Or ActiveSheet.Cells(7, r) = 3 Or ActiveSheet.Cells(7, r) = 4 Or ActiveSheet.Cells(7, r) = 5 Or ActiveSheet.Cells(7, r) = 6 Or ActiveSheet.Cells(7, r) = 7 Or ActiveSheet.Cells(7, r) = 8 Or ActiveSheet.Cells(7, r) = 9 Then tog = 1
    If tog = 1 Then a = MsgBox("Value exceeds range, negative value, non-integer value or non-numerical character in start range", vbCritical, "Error!")
    If tog = 0 Then GoTo 10
Next r

For r = 112 To 115
    tog = 1
    If ActiveSheet.Cells(10, r).Value = 0 Or ActiveSheet.Cells(10, r) = 1 Or ActiveSheet.Cells(10, r) = 2 Or ActiveSheet.Cells(10, r) = 3 Or ActiveSheet.Cells(10, r) = 4 Or ActiveSheet.Cells(10, r) = 5 Or ActiveSheet.Cells(10, r) = 6 Or ActiveSheet.Cells(10, r) = 7 Or ActiveSheet.Cells(10, r) = 8 Or ActiveSheet.Cells(10, r) = 9 Then tog = 0
    If tog = 1 Then a = MsgBox("Value exceeds range, negative value, non-integer value or non-numerical character in end range", vbCritical, "Error!")
    If tog = 1 Then GoTo 10
Next r

If ((start4) + (start3 * 10) + (start2 * 100) + (start1 * 1000)) > ((end4) + (end3 * 10) + (end2 * 100) + (end1 * 1000)) Then tog = 2
If tog = 2 Then a = MsgBox("End range smaller than start range", vbCritical, "Error!")
If tog = 2 Then GoTo 10

If (((end4) + (end3 * 10) + (end2 * 100) + (end1 * 1000)) - ((start4) + (start3 * 10) + (start2 * 100) + (start1 * 1000))) > 49 Then tog = 3
If tog = 3 Then a = MsgBox("Range Exceeds 50 Labels", vbCritical, "Error!")
If tog = 3 Then GoTo 10

start4 = start4 - 1
Var = start4
ange = ActiveSheet.Cells(1, 2)

For i = Var To ange
    Sheets("Front Page").Select
       start4 = start4 + 1
       If start4 > 9 Then
       start3 = start3 + 1
       start4 = 0
       End If
       If start3 > 9 Then
       start2 = start2 + 1
       start3 = 0
       End If
       If start2 > 9 Then
       start1 = start1 + 1
       start2 = 0
       End If
       
 ActiveSheet.Cells(12, 110).Value = start1
 ActiveSheet.Cells(12, 111).Value = start2
 ActiveSheet.Cells(12, 112).Value2 = start3
 ActiveSheet.Cells(12, 113).Value2 = start4
 
       start4 = start4 + 1
       If start4 > 9 Then
       start3 = start3 + 1
       start4 = 0
       End If
       If start3 > 9 Then
       start2 = start2 + 1
       start3 = 0
       End If
       If start2 > 9 Then
       start1 = start1 + 1
       start2 = 0
       End If
       
 ActiveSheet.Cells(13, 110).Value = start1
 ActiveSheet.Cells(13, 111).Value = start2
 ActiveSheet.Cells(13, 112).Value2 = start3
 ActiveSheet.Cells(13, 113).Value2 = start4
 
If ((start4) + (start3 * 10) + (start2 * 100) + (start1 * 1000)) > ActiveSheet.Cells(1, 2) Then tog = 4
If tog = 4 Then GoTo 10
 
 Sheets("Print Page").Select
    ActiveSheet.Range("A1:CY7").Select
    Selection.PrintOut Copies:=1, Collate:=True
    
Next i

10
Sheets("Front Page").Select

End Sub

I am new to macro could you please help me correct where I am going wrong with this code. I can print if the value of last 2 digit is numeric but cant print if the value is non numeric.

I want to print the barcode labels

For Example : if my input is like DA-01-02 and DA-01-10. I can generate label and print but if the value is like DA-01-AA and DA-01-BA it does not print? can anyone help me on this

thank you

Private Sub CommandButton1_Click()

a = MsgBox("Are you sure you want to print labels?", vbYesNo + vbQuestion, "Attention!")
If a = 7 Then GoTo 10


Set start1 = ActiveSheet.Cells(7, 112)
Set start2 = ActiveSheet.Cells(7, 113)
Set start3 = ActiveSheet.Cells(7, 114)
Set start4 = ActiveSheet.Cells(7, 115)
Set end1 = ActiveSheet.Cells(10, 112)
Set end2 = ActiveSheet.Cells(10, 113)
Set end3 = ActiveSheet.Cells(10, 114)
Set end4 = ActiveSheet.Cells(10, 115)
 
tog = 0
For r = 2 To 38
    If Mid(ActiveSheet.Cells(5, 114), 1, 1) = ActiveSheet.Cells(20, r) Then tog = 1
Next r
    If tog = 0 Then a = MsgBox("Invalid Aisle", vbCritical, "Error!")
    If tog = 0 Then GoTo 10

tog = 0
For s = 2 To 38
    If Right(ActiveSheet.Cells(5, 114), 1) = ActiveSheet.Cells(20, s) Then tog = 1
Next s
    If tog = 0 Then a = MsgBox("Invalid Aisle", vbCritical, "Error!")
    If tog = 0 Then GoTo 10
 
For r = 112 To 115
    tog = 0
    
    
    If ActiveSheet.Cells(7, r).Value = 0 Or ActiveSheet.Cells(7, r) = 1 Or ActiveSheet.Cells(7, r) = 2 Or ActiveSheet.Cells(7, r) = 3 Or ActiveSheet.Cells(7, r) = 4 Or ActiveSheet.Cells(7, r) = 5 Or ActiveSheet.Cells(7, r) = 6 Or ActiveSheet.Cells(7, r) = 7 Or ActiveSheet.Cells(7, r) = 8 Or ActiveSheet.Cells(7, r) = 9 Then tog = 1
    If tog = 1 Then a = MsgBox("Value exceeds range, negative value, non-integer value or non-numerical character in start range", vbCritical, "Error!")
    If tog = 0 Then GoTo 10
Next r

For r = 112 To 115
    tog = 1
    If ActiveSheet.Cells(10, r).Value = 0 Or ActiveSheet.Cells(10, r) = 1 Or ActiveSheet.Cells(10, r) = 2 Or ActiveSheet.Cells(10, r) = 3 Or ActiveSheet.Cells(10, r) = 4 Or ActiveSheet.Cells(10, r) = 5 Or ActiveSheet.Cells(10, r) = 6 Or ActiveSheet.Cells(10, r) = 7 Or ActiveSheet.Cells(10, r) = 8 Or ActiveSheet.Cells(10, r) = 9 Then tog = 0
    If tog = 1 Then a = MsgBox("Value exceeds range, negative value, non-integer value or non-numerical character in end range", vbCritical, "Error!")
    If tog = 1 Then GoTo 10
Next r

If ((start4) + (start3 * 10) + (start2 * 100) + (start1 * 1000)) > ((end4) + (end3 * 10) + (end2 * 100) + (end1 * 1000)) Then tog = 2
If tog = 2 Then a = MsgBox("End range smaller than start range", vbCritical, "Error!")
If tog = 2 Then GoTo 10

If (((end4) + (end3 * 10) + (end2 * 100) + (end1 * 1000)) - ((start4) + (start3 * 10) + (start2 * 100) + (start1 * 1000))) > 49 Then tog = 3
If tog = 3 Then a = MsgBox("Range Exceeds 50 Labels", vbCritical, "Error!")
If tog = 3 Then GoTo 10

start4 = start4 - 1
Var = start4
ange = ActiveSheet.Cells(1, 2)

For i = Var To ange
    Sheets("Front Page").Select
       start4 = start4 + 1
       If start4 > 9 Then
       start3 = start3 + 1
       start4 = 0
       End If
       If start3 > 9 Then
       start2 = start2 + 1
       start3 = 0
       End If
       If start2 > 9 Then
       start1 = start1 + 1
       start2 = 0
       End If
       
 ActiveSheet.Cells(12, 110).Value = start1
 ActiveSheet.Cells(12, 111).Value = start2
 ActiveSheet.Cells(12, 112).Value2 = start3
 ActiveSheet.Cells(12, 113).Value2 = start4
 
       start4 = start4 + 1
       If start4 > 9 Then
       start3 = start3 + 1
       start4 = 0
       End If
       If start3 > 9 Then
       start2 = start2 + 1
       start3 = 0
       End If
       If start2 > 9 Then
       start1 = start1 + 1
       start2 = 0
       End If
       
 ActiveSheet.Cells(13, 110).Value = start1
 ActiveSheet.Cells(13, 111).Value = start2
 ActiveSheet.Cells(13, 112).Value2 = start3
 ActiveSheet.Cells(13, 113).Value2 = start4
 
If ((start4) + (start3 * 10) + (start2 * 100) + (start1 * 1000)) > ActiveSheet.Cells(1, 2) Then tog = 4
If tog = 4 Then GoTo 10
 
 Sheets("Print Page").Select
    ActiveSheet.Range("A1:CY7").Select
    Selection.PrintOut Copies:=1, Collate:=True
    
Next i

10
Sheets("Front Page").Select

End Sub

• excel
• vba

Share Improve this question Follow edited Feb 26 at 8:48 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 30 at 6:03 P fernsP ferns 111 bronze badge 9

• 1 If ((start4) + (start3 * 10)... how does this math work if those variables are letters and not digits? – Tim Williams Commented Jan 30 at 6:12
• 2 There are 3 sheet referenced in your code - ActiveSheet, "Font page" and "Print page". It's pretty much impossible to know which is which. Declare and set a couple of worksheet variables and use those in place of all instances of ActiveSheet. – Tim Williams Commented Jan 30 at 18:35
• Guessing ActiveSheet is not so bad in this case as it starts from sheet button is on and then code sets focus to other sheets with Select. However, code should be possible without having to "jump around". I would either set sheet object variables or use With ... End With block and avoid the confusing ActiveSheet. I avoid Select and never have used ActiveAnything. – June7 Commented Jan 30 at 21:12
• For instance, the 3 lines to Select and print range could be reduced to one: Sheets("Print Page").Range("A1:CY7").PrintOut - parameters of 1 and True are defaults so can be omitted. – June7 Commented Jan 30 at 21:47
• Perhaps concatenate the 4 characters and then use expression to determine if start is less than end: "0110"<"01AA" ("0210" would not be less than "01AA" - does that fit your situation?). Calculating range difference is complicated by alpha references. How high could numbers go in the last 2 places - 99? Does use of alphas mean exceeding 99? Don't use Set unless setting object variables, not for string or number values. – June7 Commented Jan 30 at 23:08

 |  Show 4 more comments

1 Answer 1

Sorted by: Reset to default 0

Perhaps this will get you on the right path. Some of the code purpose and logic is not clear to me (such as the 2 To 38 loop and the loop at end of procedure). Note elimination of ActiveSheet and Select. Code assumes every input cell has a value. Could add code that first verifies each cell is not empty.

Private Sub PrintLabels()

a = MsgBox("Are you sure you want to print labels?", vbYesNo + vbQuestion, "Attention!")
If a = 7 Then Exit Sub

With Sheets("Front Page")
    start1 = .Cells(7, 112)
    start2 = .Cells(7, 113)
    start3 = .Cells(7, 114)
    start4 = .Cells(7, 115)
    end1 = .Cells(10, 112)
    end2 = .Cells(10, 113)
    end3 = .Cells(10, 114)
    end4 = .Cells(10, 115)
    
    For r = 2 To 38
        If Mid(.Cells(5, 114), 1, 1) = .Cells(20, r) Then tog1 = 1
        If Right(.Cells(5, 114), 1) = .Cells(20, r) Then tog2 = 1
    Next
    If tog1 = 0 Or tog2 = 0 Then
        MsgBox "Invalid Aisle", vbCritical, "Error!"
        Exit Sub
    End If
    If Not (IsNumeric(start1 & start2) And IsNumeric(end1 & end2)) Or _
        Not (IsNumeric(start3 & start4) Or (start3 Like "[A-z]" And start4 Like "[A-z]")) Or _
        Not (IsNumeric(end3 & end4) Or (end3 Like "[A-z]" And end4 Like "[A-z]")) Then
        MsgBox "One or more range inputs not valid", vbCritical, "Error"
        Exit Sub
    End If
    If IsNumeric(start3 & start4) Then
        s = Val(start1 & start2 & start3 & start4)
    Else
        s = Val((start1 & start2) * 100 + 99 + Range(start3 & start4 & 1).column - 26)
    End If
    If IsNumeric(end3 & end4) Then
        e = Val(end1 & end2 & end3 & end4)
    Else
        e = Val((end1 & end2) * 100 + 99 + Range(end3 & end4 & 1).column - 26)
    End If
    If s > e Then
        MsgBox "End range smaller than start range", vbCritical, "Error!"
        Exit Sub
    End If
    If e - s > 49 Then
        MsgBox "Range Exceeds 50 Labels", vbCritical, "Error!"
        Exit Sub
    End If
    If Not IsNumeric(start4) Then start4 = 10 + Range(start4 & 1).column
    start4 = start4 - 1
    Var = start4
    If Not IsNumeric(start3) Then start3 = 9 + Range(start3 & 1).column
    ange = .Cells(1, 2)

    For i = Var To ange
        start4 = start4 + 1
        If start4 > 9 Then
           start3 = start3 + 1
           start4 = 0
        End If
        If start3 > 9 Then
           start2 = start2 + 1
           start3 = 0
        End If
        If start2 > 9 Then
           start1 = start1 + 1
           start2 = 0
        End If
           
        .Cells(12, 110) = start1
        .Cells(12, 111) = start2
        .Cells(12, 112) = start3
        .Cells(12, 113) = start4
     
        start4 = start4 + 1
        If start4 > 9 Then
           start3 = start3 + 1
           start4 = 0
        End If
        If start3 > 9 Then
           start2 = start2 + 1
           start3 = 0
        End If
        If start2 > 9 Then
           start1 = start1 + 1
           start2 = 0
        End If
           
        .Cells(13, 110) = start1
        .Cells(13, 111) = start2
        .Cells(13, 112) = start3
        .Cells(13, 113) = start4
         
        If Val(start1 & start2 & start3 & start4) > .Cells(1, 2) Then Exit Sub
        Sheets("Print Page").Range("A1:CY7").PrintOut
    Next i
End With

End Sub

本文标签： excelVba code to print multiple a4 size label with barcodeStack Overflow