PDA

View Full Version : auto numbered Vouchers need tracking sheet



cabotiger
10-08-2010, 06:29 PM
I have an excel file that when it prints the vouchers requested it will auto number them. I am trying to find a way that i can create a log of all the numbers that were generated and than have it print after the voucher print.

I am lost on how to get the numbers from the vouchers to the tracking sheet.

Any Ideas?

mbarron
10-08-2010, 09:00 PM
I've updated your workbook.
Changes:
When opened, the user form will use the last entry, which will be the number of the last printed ticket, from sheet two to populate the 'From' text box.

After printing, the number of the last printed ticket will be added to the bottom of the list. Currently it only adds the last number, not all numbers printed.

mbarron
10-08-2010, 09:04 PM
I forgot to mention. For your print routine, I changed:
Do Until z = y
to
Do Until z >= y
otherwise it'll be printing ticket forever. z will never equal y with the current structure of the code. Once it loops through, z = 11 while y = 10

mbarron
10-08-2010, 09:09 PM
If you want it to print each individual ticket number, use this for your Print routine:
Private Sub cmdPrint_Click()
Dim x As Long, y As Long, z As Long

x = Me.TextBox1.Value
y = Me.TextBox2.Value

z = x - 0
Unload Me ' if not Excel will lock in Print Preview

On Error GoTo whoops
Do Until z >= y
Range("c13").Value = z + 1
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c27").Value = z + 2
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c41").Value = z + 3
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c55").Value = z + 4
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("c69").Value = z + 5
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f13").Value = z + 6
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f27").Value = z + 7
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f41").Value = z + 8
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f55").Value = z + 9
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
Range("f69").Value = z + 10
Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1) = z
z = z + 10
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Loop


whoops:
UserForm1.Show

End Sub

cabotiger
10-08-2010, 11:22 PM
OK so i made the changes you guys suggested and everything seems to be working but.......
when i tell it to print 1 and 6 batches the very first voucher on the sheet says either TRUE or FALSE. when i look at it in the print preview mode i do not see that.

And

If i tell it to start printing at 11 the first number on the log is always 12..

mbarron
10-09-2010, 11:40 AM
Try the attached version.