Before Print for All Selected sheets.

  • Thread starter Thread starter beeawwb
  • Start date Start date
B

beeawwb

Good afternoon all. Getting quite frustrated trying to code this myself
and I just can't figure out the correct syntax for what I want to do.

I have a Workbook BeforePrint() sub that obviously, gets called whe
the user goes to printpreview, or prints the workbook (I have a prin
dialogue box that does this for me).

What I want to do, is add the following code to each selecte
worksheet, or all worksheets if none selected.

ActiveSheet.PageSetup.LeftHeader = "Printed By "
Application.UserName

How would I get Excel to add this header ("Printed By "
Application.UserName) to each sheet that is being printed? Thanks fo
any help or suggestions you can provide.

-Bo
 
Hi Bob
try putting the following code in your workbook module:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ActiveWindow.SelectedSheets
With wkSht.PageSetup
.LeftHeader = "Printed By " & Application.UserName
End With
Next wkSht
End Sub

Frank
 
I like Frank's code, but I think I'd do all the sheets each time. If they don't
print them, it shouldn't hurt much.

But if they do file|print|entire workbook, you won't have to worry.
 
Hi Dave
good idea. Your solution is especially better when you're trying to
print the whole workbook (without selecting all worksheets but using
the respective print option). My former code will only insert header
info for the current selected sheet(s).

So for the OP you may use the following:
----
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ActiveWindow.Worksheets
With wkSht.PageSetup
.LeftHeader = "Printed By " & Application.UserName
End With
Next wkSht
End Sub

Frank
 
Ok, now I just need to check, the second solution will change the heade
for every sheet, regardless of whether it is being printed or not
That's the best solution in my opinion, as the entire book gets printe
once a week, with various sheets being printed at random times.

Thanks for your help guys. :)

-Bo
 
What the...? I can't seem to figure out why this is happening, bu
whenever I go to PrintPreview, it tells me that "Run-Time Error '438'
Object doesn't support this property or method'"

For Each wkSht In ActiveWindow.Worksheets

is highlighted.

Thanks for anything you can help with.

-Bo
 
Hi
yes that's right. The code will change the header/footer for all sheets
in your workbook whether printed or not.
 
Hi
typical copy+pase error on my side. change the line to
For Each wkSht In Me.Worksheets
 
Back
Top