PhilC
10-06-2010, 12:12 PM
:bug: I hope that someone can see what I'm not seeing in my VBA application.
I am using VBA to create a user form in Excel that is used to collect the data to print labels and then, when the process command button is clicked, the labels are printed.
My code collects data from the start label textbox, endlabel textbox or number of labels textbox, and the site. The logic then calculates the number of labels to print and builds a string in the label printer language (ZPL- Zebra programming Language) and "prints" the format string to the driver with the passthrough characters at the beginning and end of the string.
When the workbook is first opened (I use an on open event to show the form and hide the spreadsheet) and the data is filled in the printer prints as expected. However, If I change the data and try to print again I get every other expected print. For example the normal print would be the following sequence:
ABCD0001
ABCD0002
ABCD0003
ABCD0004 and so on. If the data is changed on the form and the process button is pressed again the output that is actually printed becomes:
ABCD0001
ABCD0003
ABCD0005 and so on. So every other label data printout is dumped. Is this related to my code or is this related to the printer? Any suggestions on how to resolve this issue so that the form process button, prints the correct info every time?
Private Sub CommandButton1_Click()
'check text box values and pass values to main module
sl = StartLabel.Text
el = EndLabel.Text
Site = Site.Text
If Len(sl) = 0 Or Len(el) = 0 Then
MsgBox "Please enter Start and End values"
Exit Sub
End If
If Len(Site) = 0 Then
ans = MsgBox("Are you sure that you do not want to enter a Site name?" & Chr(13) & _
"The Default 'E-Team' will be printed without a site name", vbYesNo, "Site Name?")
If ans = vbNo Then Exit Sub
End If
' label formatting commands
s = "^XA^PR2~SD30" ' start of label format
f = "^XZ" ' end of label format
O1 = "^FO0,20" ' origin ^FOx,y
O2 = "^FO40,78" ' origin ^FOx,y
O3 = "^FO0,150" ' origin ^FOx,y
FNT = "T" ' font selector
F1 = "^AUN,62,7" ' field 1 font
F2 = "^B3N,,62,N" ' Barcode font
F3 = "^ADN,36,4" ' site name font
DS = "^FD" 'data field start
DE = "^FS" 'data field seperator
FB = "^FB400,1,0,C,0" ' Fields (F1 & F3) needs this at the beginning
''Loop resolution
Call lpcnt(sl, el, sv, svl, ev, evl, lp)
iter = lp
MsgBox lp & " Labels will be printed." & Chr(13) & "Starting at " & sl & " and ending at " & el & "."
nl = sl
'build and print labels
For I = 1 To iter
Str1 = "${" & s & O1 & F1 & FB & DS & nl & DE ' Start of format data
Str2 = O2 & F2 & DS & nl & DE ' Barcode portion of label
Str3 = O3 & F3 & FB & DS & "E-TEAM/" & Site & f & "}$" 'end of format data
Worksheets("LabelData").Range("$A$1") = Str1 & Str2 & Str3 ' build and save command codes
'************************************************
Worksheets("LabelData").Range("$A$1").PrintOut ' send data to passthrough on driver
'Repeat until all labels are printed
' nl = next label, sv1 = length of non number start label
' lz = leading zeros, nv = next value string
x = Len(nl)
For Y = 1 To Len(nl)
If Val(Mid(nl, Y, 1)) = 0 And Mid(nl, Y, 1) <> "0" Then x = x - 1
Next Y
nvn = Val(Right(nl, x)) + 1 ' next value number
nvl = Len(nl) - x ' length of numbers string, x = length of non number string
nv = CStr(nvn) ' next value string
If Len(CStr(nv)) < nvl Then
lz = ""
For h = 1 To (Len(nl) - (x + Len(nv)))
lz = lz & "0"
Next h
End If
nl = Left(nl, x) & lz & nv
Next I
Call UserForm_Initialize
End Sub
Private Sub lpcnt(sl, el, sv, svl, ev, evl, lp)
'Determine numeric start value
x = Len(sl)
For I = 1 To Len(sl)
If Val(Mid(sl, I, 1)) = 0 And Mid(sl, I, 1) <> "0" Then x = x - 1
Next I
sv = Val(Right(sl, x))
svl = Len(sl) - x
'Determine numeric end value
x = Len(el)
For I = 1 To Len(el)
If Val(Mid(el, I, 1)) = 0 And Mid(el, I, 1) <> "0" Then x = x - 1
Next I
ev = Val(Right(el, x))
evl = Len(el) - x
' Calculate number of labels to be printed.
lp = ev - sv + 1
End Sub
I am using VBA to create a user form in Excel that is used to collect the data to print labels and then, when the process command button is clicked, the labels are printed.
My code collects data from the start label textbox, endlabel textbox or number of labels textbox, and the site. The logic then calculates the number of labels to print and builds a string in the label printer language (ZPL- Zebra programming Language) and "prints" the format string to the driver with the passthrough characters at the beginning and end of the string.
When the workbook is first opened (I use an on open event to show the form and hide the spreadsheet) and the data is filled in the printer prints as expected. However, If I change the data and try to print again I get every other expected print. For example the normal print would be the following sequence:
ABCD0001
ABCD0002
ABCD0003
ABCD0004 and so on. If the data is changed on the form and the process button is pressed again the output that is actually printed becomes:
ABCD0001
ABCD0003
ABCD0005 and so on. So every other label data printout is dumped. Is this related to my code or is this related to the printer? Any suggestions on how to resolve this issue so that the form process button, prints the correct info every time?
Private Sub CommandButton1_Click()
'check text box values and pass values to main module
sl = StartLabel.Text
el = EndLabel.Text
Site = Site.Text
If Len(sl) = 0 Or Len(el) = 0 Then
MsgBox "Please enter Start and End values"
Exit Sub
End If
If Len(Site) = 0 Then
ans = MsgBox("Are you sure that you do not want to enter a Site name?" & Chr(13) & _
"The Default 'E-Team' will be printed without a site name", vbYesNo, "Site Name?")
If ans = vbNo Then Exit Sub
End If
' label formatting commands
s = "^XA^PR2~SD30" ' start of label format
f = "^XZ" ' end of label format
O1 = "^FO0,20" ' origin ^FOx,y
O2 = "^FO40,78" ' origin ^FOx,y
O3 = "^FO0,150" ' origin ^FOx,y
FNT = "T" ' font selector
F1 = "^AUN,62,7" ' field 1 font
F2 = "^B3N,,62,N" ' Barcode font
F3 = "^ADN,36,4" ' site name font
DS = "^FD" 'data field start
DE = "^FS" 'data field seperator
FB = "^FB400,1,0,C,0" ' Fields (F1 & F3) needs this at the beginning
''Loop resolution
Call lpcnt(sl, el, sv, svl, ev, evl, lp)
iter = lp
MsgBox lp & " Labels will be printed." & Chr(13) & "Starting at " & sl & " and ending at " & el & "."
nl = sl
'build and print labels
For I = 1 To iter
Str1 = "${" & s & O1 & F1 & FB & DS & nl & DE ' Start of format data
Str2 = O2 & F2 & DS & nl & DE ' Barcode portion of label
Str3 = O3 & F3 & FB & DS & "E-TEAM/" & Site & f & "}$" 'end of format data
Worksheets("LabelData").Range("$A$1") = Str1 & Str2 & Str3 ' build and save command codes
'************************************************
Worksheets("LabelData").Range("$A$1").PrintOut ' send data to passthrough on driver
'Repeat until all labels are printed
' nl = next label, sv1 = length of non number start label
' lz = leading zeros, nv = next value string
x = Len(nl)
For Y = 1 To Len(nl)
If Val(Mid(nl, Y, 1)) = 0 And Mid(nl, Y, 1) <> "0" Then x = x - 1
Next Y
nvn = Val(Right(nl, x)) + 1 ' next value number
nvl = Len(nl) - x ' length of numbers string, x = length of non number string
nv = CStr(nvn) ' next value string
If Len(CStr(nv)) < nvl Then
lz = ""
For h = 1 To (Len(nl) - (x + Len(nv)))
lz = lz & "0"
Next h
End If
nl = Left(nl, x) & lz & nv
Next I
Call UserForm_Initialize
End Sub
Private Sub lpcnt(sl, el, sv, svl, ev, evl, lp)
'Determine numeric start value
x = Len(sl)
For I = 1 To Len(sl)
If Val(Mid(sl, I, 1)) = 0 And Mid(sl, I, 1) <> "0" Then x = x - 1
Next I
sv = Val(Right(sl, x))
svl = Len(sl) - x
'Determine numeric end value
x = Len(el)
For I = 1 To Len(el)
If Val(Mid(el, I, 1)) = 0 And Mid(el, I, 1) <> "0" Then x = x - 1
Next I
ev = Val(Right(el, x))
evl = Len(el) - x
' Calculate number of labels to be printed.
lp = ev - sv + 1
End Sub