Geekamo
asked on
VBA Code - Loops, Counters, & Stops
Hello Experts,
Please bare with me as I explain this,...
I am looking for a VBA code that involves loops & counters (or at least I think that's what is needed).
Things you'll need to know...
Worksheets: Input / Preview
Named Cells: TotalPallets / PrintSelection
Excisting Print Code:
See attached file...
As you can see, there are "4" total pallets. Along with the units & weights of each pallet listed to the right.
If you click on the Preview worksheet, you'll see it reflects information of Pallet # 1.
If you change the "Print Pallet Tag" field from 1, to 2 - then click on Preview - you'll see the Preview worksheet now reflects information about Pallet # 2.
So basically, there is ONE preview page - that is dynamic, and the user controls what page they are previewing by adjusting the "Print Pallet Tag" #.
Currently, when the user wants to print the pallet tags they have to put 1 into the field, then hit the print button, then they put 2 in the field, then print, and so on...
Obviously after a while, this is annoying. Ideally, I would like a print all option. But in order for it to work correctly - the VBA code will need to change the number in the "Print Pallet Tag" field.
Assuming I havn't lost you,... Here is what I would like...
Step 1: Code looks to named cell "PrintSelection".
Step 2: IF value equals a #, then execute this line of code:
Step 3: After that code executed, code ends.
Step 4: IF value of "PrintSelection" equals nothing, then assume Print All.
Step 5: Code looks at named cell "TotalPallets".
Step 6: The value in TotalPallets, would represent the # to stop on.
Step 7: Code puts a "1" into PrintSelection, then runs the print code
Step 8: Code puts a "2" into PrintSelection, then runs the print code.
Step 9: Code puts a "3" into PrintSelection, then runs the print code.
It repeats those steps, until it reaches the TotalPallets number then stops.
I HOPE this made sense. if you have any questions - please let me know.
Thank you in advance!
~ Geekamo
Pallet-Tag-Generator--Must-Enabl.xlsm
Please bare with me as I explain this,...
I am looking for a VBA code that involves loops & counters (or at least I think that's what is needed).
Things you'll need to know...
Worksheets: Input / Preview
Named Cells: TotalPallets / PrintSelection
Excisting Print Code:
Sub Pallet_Tag_Print()
ThisWorkbook.Worksheets("Preview").PrintOut
End Sub
See attached file...
As you can see, there are "4" total pallets. Along with the units & weights of each pallet listed to the right.
If you click on the Preview worksheet, you'll see it reflects information of Pallet # 1.
If you change the "Print Pallet Tag" field from 1, to 2 - then click on Preview - you'll see the Preview worksheet now reflects information about Pallet # 2.
So basically, there is ONE preview page - that is dynamic, and the user controls what page they are previewing by adjusting the "Print Pallet Tag" #.
Currently, when the user wants to print the pallet tags they have to put 1 into the field, then hit the print button, then they put 2 in the field, then print, and so on...
Obviously after a while, this is annoying. Ideally, I would like a print all option. But in order for it to work correctly - the VBA code will need to change the number in the "Print Pallet Tag" field.
Assuming I havn't lost you,... Here is what I would like...
Step 1: Code looks to named cell "PrintSelection".
Step 2: IF value equals a #, then execute this line of code:
ThisWorkbook.Worksheets("Preview").PrintOut
Step 3: After that code executed, code ends.
Step 4: IF value of "PrintSelection" equals nothing, then assume Print All.
Step 5: Code looks at named cell "TotalPallets".
Step 6: The value in TotalPallets, would represent the # to stop on.
Step 7: Code puts a "1" into PrintSelection, then runs the print code
Step 8: Code puts a "2" into PrintSelection, then runs the print code.
Step 9: Code puts a "3" into PrintSelection, then runs the print code.
It repeats those steps, until it reaches the TotalPallets number then stops.
I HOPE this made sense. if you have any questions - please let me know.
Thank you in advance!
~ Geekamo
Pallet-Tag-Generator--Must-Enabl.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't have access to a printer while testing this at home. In order to test this, I placed a " ' " before the two lines of print code to disable them. Then I added a MsgBox right above them, so that way I can see the code step through all the loops.
It has worked beautifully! I really appreciate the time you spent on this. Thank you so much!
~ Geekamo