VBA - could it override defaults in excel

P

pboost1

I found out that Excel 2003 has VBA built in. Could VBA be used to hav
certain sections of the workbook print to different printers? If so
what would be needed? The Driver for the printer, or just the printe
itself? Thank you for any help on this
 
D

Dave Peterson

Printers can go by different names. This is especially true if they're network
printers.

The easiest way to find out what to do might be to record a macro when you do it
one time.

Tools|Macro|Record new macro
give it a nice name and select your range/sheet and print it.
change printers
select your range/sheet and print it
change printers back

Stop recording

Hit alt-f11 to get to the VBE (where that recorded macro lives)

You'll see the code. You can tweak it to your heart's content. (copy it to
notepad first--just in case you damage it too much.)

If you have trouble, post back with the details (not the workbook).
 
P

pboost1

here is the VBA

Sub printertest()
'
' printertest Macro
' Macro recorded 8/14/2004 by Steven Baer
'

'
Sheets("new").Select
Application.ActivePrinter = "HPBNACC on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HPBNACC on Ne01:", Collate:=True
Sheets("billing").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("late").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("yellow front").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("yellow back").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("green").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Application.ActivePrinter = "CRADEN DP6 on COM1:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CRADEN DP6 on COM1:"
Sheets("yellow back").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("yellow front").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("late").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("billing").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("new").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Application.ActivePrinter = "HPBNACC on Ne01:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"HPBNACC on Ne01:", Collate:=True
Sheets("billing").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("late").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("yellow front").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("yellow back").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("green").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub



These six sheets I would like to print to the craden printer when th
print button is pressed. I'm not sure what to do in VBA to get it t
print to the craden printer. I don't want to edit this for possibilit
of messing something up. Thank you in advance
 
D

Dave Peterson

Instead of using the print button on the menubar, just rerun your macro.

tools|macro|macros, select PrinterTest and click Run.

You could run it from a shortcut key
tools|macro|macros, select printertest and click options and give it a nice
shortcut key combination.

You could add a button to your favorite toolbar
tools|customize|commands tab|Macros
drag an icon to your favorite toolbar
(you could even create a new one using the Toolbars Tab)
and assign your macro to that button.

Or you could show the Forms toolbar
View|toolbars|show the Forms toolbar
find the button icon and click on it.
Now back to your worksheet (any worksheet) and draw the button
(top left to bottom right)
When you let go of the button, you'll be asked for the macro name.
choose PrinterTest.
 
P

pboost1

The problem is, these pages get printed on different days, and differen
times. I was just trying to have it where pressing the print ico
would print these pages to the craden printer
 
D

Dave Peterson

But the bad news is that if you steal that button's function, then you can't use
it for anything else.

There is a workbook_beforeprint event that you could tie into, but I would think
that the easiest way (and most straight forward) is to just make that macro do
what you want.
 
Top