• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

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:  

Sub Pallet_Tag_Print()
ThisWorkbook.Worksheets("Preview").PrintOut
End Sub

Open in new window


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

Open in new window


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
0
Geekamo
Asked:
Geekamo
1 Solution
 
agillandersCommented:
Try the attached. I added two small macros to Module 6.

Pallet_Tag_PrintSelector - decides which type of print (all or one) is required
Pallet_Tag_PrintAll - which prints all pallet tags from 1 to TotalPallets

Regards

Alistair
PalletTag-Test.xlsm
0
 
GeekamoAuthor Commented:
@ Alistair,

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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now