Creating a label print counter and counter reset

alh

New Member
Joined
May 5, 2022
Messages
3
Platform
  1. Windows
Hi,

I am using Excel 365.
I am trying to create a macro to have a counter on my labels.
I have used the following which works fine:
Sub Print_many()

c = InputBox("Insert amount of labels to print")
For w = Range("B6") + 1 To Range("B6") + c
Range("B6") = w
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next w

Range("B6") = w

End Sub


However, I also to reset the count so if I need to print again it can start back on "1" with a different macro.

I was also wondering if it is possible to choose the printer?

Is that something that is possible to do?
Thanks beforehand for your answers!!
Alexandra
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
Why are you storing a number in B6 that is associated with the labels if you want the number to reset each time? I guess I'm not really following.
Also, every time this executes: Range("B6") = w B6 increments by one, so there's no need to have that at the end of the code.
Maybe you need a userform so that user can input starting value should as well as count? With that you could provide just about any inputs, such as start, begin and even increment (e.g. by two's).
 

alh

New Member
Joined
May 5, 2022
Messages
3
Platform
  1. Windows
Hi Micron,
So what I'm trying to do is create a label with a counter so I can have written on the label: Bag 1 - Bag 2 ... and so on.
I would just like to ensure that when the macro is run it always starts with label marked as bag 1 so as not to confuse the users.
There might be an easier way?
Thanks a lot for any help! I have never used macro before!
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
You didn't explain what's going on with B6 so this is just a guess but at least it should answer how to pick a printer.
VBA Code:
Sub Print_many()

c = InputBox("Insert amount of labels to print")
Application.Dialogs(xlDialogPrinterSetup).Show
For w = Range("B6") + 1 To Range("B6") + c
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next w

End Sub
 

alh

New Member
Joined
May 5, 2022
Messages
3
Platform
  1. Windows
Sorry, B6 is the cell where my counter appears! See below picture, does it make sense?

1652092910370.png

Thanks for the printer pick, that works great!
 

Attachments

  • 1652092417389.png
    1652092417389.png
    7.9 KB · Views: 1
  • 1652092441872.png
    1652092441872.png
    11.9 KB · Views: 1

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,773
Office Version
  1. 365
Platform
  1. Windows
Why not just type in a number in B6 rather than have a button and code for such a simple thing? You could then leave the code as is and your options would be continue from last number, start from 0, or type in any number you want and start from there.

Or use code for your reset button to reset to 0:
Sub Reset()
Range("B6") = 0
End Sub

Or forget the button and change your existing code to (this one does not use B6 at all)
VBA Code:
c = InputBox("Insert amount of labels to print")
Application.Dialogs(xlDialogPrinterSetup).Show
For w = 1 To c
   ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next w
I think it makes more sense to just type a number into B6.
 
Solution

Forum statistics

Threads
1,176,093
Messages
5,901,367
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top