Macro?

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Can you set up an Excel Workbook that when you click on
the different sheets it will automatically change the
print setup to the desired printer and optional setting?

Ben
 
Ben, how about something like this, put in the worksheet code

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.ActivePrinter = "HP DeskJet 720C Series on LPT1:"

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "This is a test"
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.25)
.BottomMargin = Application.InchesToPoints(0.25)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
End With

Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Paul,

I tried the following but I must be entering it wrong
and/or in the wrong place. When you say put this in the
worksheet code is that a macro or somewhere else? Thanks
for your time!!!!

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.ActivePrinter = "Brother PT-2300/2310 on
PTUSB(PT-2300/2310-D3J541009):"

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.03)
.RightMargin = Application.InchesToPoints(0.03)
.TopMargin = Application.InchesToPoints(0.15)
.BottomMargin = Application.InchesToPoints(0.3)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 300
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.LabelFormat = Job_Folder
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = True
.Zoom = 100
End With

Application.ScreenUpdating = True
End Sub
 
To put in this macro right click on the worksheet tab and view code, in the
window that opens paste this code, press Alt and Q to close this window and
go back to your workbook. If you are using excel 2000 or newer you may have
to change the macro security settings to get the macro to run. Now click on
another worksheet tab then select the tab where you put the code and it will
run automatically each time you select the sheet.

Also you may not need the page setup part to run each time, you can set it
up different on each worksheet tab, so you should only have to do it once
and not in the macro.
--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Paul,

Thanks.

I recieved the following error:

"Run-Time error '1004':

Method 'ActivePrinter' of object'_Application' failed.

???

Thanks Ben
 
It sounds like the printer name is not right, try this, in the VBA editor,
where you put the code in, do a Ctrl+G, this will open the Immediate
window, in it type, or copy and paste, Debug.Print Application.ActivePrinter
then hit enter, this will give you the name of your printer, in my case I
get this
HP DeskJet 720C Series on LPT1: then use the line like this, put the
printer name in quotes.
Application.ActivePrinter = "HP DeskJet 720C Series on LPT1:"


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Paul,

Thanks! That was it! A couple more questions.

One, what book do you recommend for learning Visual Basic.

Two, Is there a website that list the various commands
that can be used in this print feature, like setting a
duplex option etc.

Three, the label printer works now, (thanks to your help)
however it never stops spitting the tape out, is there a
way I can set the the length to 3.5" and then have it cut
it? I have tried, PaperLength =

Ben
 
P.S. One More...

Is there a command that I can enter in that Immediate
screen that would query the designated printer and list
all the available commands for that printer?

Thanks,

Ben
 
Ben, do a search here and you will find many recommendations for books and
other sources, not to mention looking at and trying the code on the
newsgroups,
http://www.google.com/advanced_group_search?q=group:*excel*&lr=lang_en&num=50&hl=en

If you record a macro when you do the set up it will record all the options
that you can do, I have never used a "label printer" only sheets of labels
on a regular printer, so I don't know about it


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Ben, Gald I could help

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Back
Top