Triggering a macro after Printing

[Sloth]

Below is some code I am working on. Basically it looks at specific cell A7.
If it is equal to zero then that row gets hidden when printing. I want all
rows to unhide after printing. How do I trigger this? The section under
Before_Save is what I would like to do after printing. Is there an easier
way to do this?



Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets("January").Select
If Cells(7, 1) = 0 Then
Rows("7:7").EntireRow.Hidden = True
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets(Array("January", "February", "March", "April", _
"May", "June", "July", "August", "September", _
"October", "November", "December", "Summary")).Select
Cells.Select
Selection.EntireRow.Hidden = False
Sheets("January").Select
Cells(1, 1).Select
End Sub

 

[Sloth]

Thanks for the help. Here is what I got. I removed the "On Error" because I
am still debugging this (I made another macro to enable the events back on).
This code seems to be working okay. But I would prefer to have the print
window when printing. The "PrintOut" command skips that and prints it
immediately to the default printer. So now I can't cancel or even perform a
print preview.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim wkSht As Worksheet
Sheets(Array("January", "February", "March", "April", _
"May", "June", "July", "August", "September", "October", _
"November", "December", "Summary")).Select
For Each wkSht In ActiveWindow.SelectedSheets
If wkSht.Cells(7, 1) = 0 Then
wkSht.Rows("7:7").EntireRow.Hidden = True
End If
Next wkSht

Cancel = True

Application.EnableEvents = False
ActiveWindow.SelectedSheets.PrintOut
Application.EnableEvents = True

For Each wkSht In ActiveWindow.SelectedSheets
wkSht.Cells.EntireRow.Hidden = False
Next wkSht
Cells(1, 1).Select
Sheets("January").Select

End Sub

 

[Sloth]

I changed PrintOut to PrintPreview. This always takes me to the printpreview
screen but then the print button (in the preview screen) works normally.
That's seems wierd to me, but it works. Is there any danger to doing it like
this?

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim wkSht As Worksheet
Sheets(Array("January", "February", "March", "April", _
"May", "June", "July", "August", "September", _
"October", "November", "December", "Summary")).Select
For Each wkSht In ActiveWindow.SelectedSheets
If wkSht.Cells(7, 1) = 0 Then
wkSht.Rows("7:7").EntireRow.Hidden = True
End If
Next wkSht

Cancel = True

Application.EnableEvents = False
ActiveWindow.SelectedSheets.PrintPreview
Application.EnableEvents = True

For Each wkSht In ActiveWindow.SelectedSheets
wkSht.Cells.EntireRow.Hidden = False
Next wkSht
Cells(1, 1).Select
Sheets("January").Select

End Sub

 

[Robert Bruce]

Below is some code I am working on. Basically it looks at specific
cell A7. If it is equal to zero then that row gets hidden when
printing. I want all rows to unhide after printing. How do I
trigger this? The section under Before_Save is what I would like to
do after printing. Is there an easier way to do this?

http://www33.brinkster.com/rbad/default.aspx?section=tips&page=newevents

--
Rob

Original portions of his message are
copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

 

[Sloth]

Thank you so much!!!! That's perfect.

http://www33.brinkster.com/rbad/default.aspx?section=tips&page=newevents

--
Rob

Original portions of his message are
copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

 

[Dana DeLouis]

Sheets(Array("January", "February", "March", "April", _
"May", "June", "July", "August", "September", _
"October", "November", "December", "Summary")).Select

As a side note... Just an alternative if you prefer:

Sheets(Application.GetCustomListContents(4)).Select
Sheets("Summary").Select False ' Don't Replace

Also, depending on what you are doing...
Rows("7:7").EntireRow.Hidden = (Cells(7, 1) = 0)