Printing Macro (2000)

Topic

New Reply

I have a spreadsheet in which a user will input a bit of information then switch from Sheet1 to Sheet2 and then print out detailed pallet tags. The problem is that many users still hit the print button when it is still on Sheet1 , they then leave and come back about a half hour later only to find they have printed out 200 copies of the wrong screen.
Could I trouble someone to offer up a quick macro that will only allow the “Print” button to work when Sheet2 is selected?
Thanks in advance for any help you can give.
Stats

Reply | Quote

Replies

Why not a macro button on sheet 1 tied to a macro that switches to sheet2 and prints. Might be easier than disabling/enabling the print button.

Just my

Reply | Quote

You could use a macro like the one below and attach it to a button on the toolbar.

Public Sub PrintSheet2()
    Worksheets("Sheet2").PrintOut Copies:=1
End Sub

Reply | Quote

Thanks Legare!!
For my application your macro is going to work great, one quick question…Is there a way for the macro to print out a user defined number of copies? I tried ‘Worksheets(“Sheet2”).PrintOut Copies:=Sheet1!$b$5’ where the user has already entered into B5 the number of copies they want, but that didn’t work. Any ideas?
Thanks again for your help.
Stats

Reply | Quote

You can’t refer to a cell in VBA the way you do in a formula. Use

Worksheets(“Sheet2”).PrintOut Copies:=Worksheets(“Sheet1”).Range(“B5”)

Reply | Quote

Or Worksheets(“Sheet1”).[b5]

Reply | Quote

Thanks Hans,
Ive only just begun to learning about macros and I can’t thank you (and Legare, and Jan…….et al) enough for all the help you give us VB challenged folks.
Thanks Again
Stats

Reply | Quote

Why not just create a BeforePrint routine for the Workbook in question and force the target to be active

Private Sub Workbook_BeforePrint(Cancel As Boolean)
activeworkbook,worksheets(“sheet2”).activate
End Sub

Reply | Quote