Using Before_Print function

[CHARLESJOSEPH]

First post: Very adept at Excel functionality but total newbie to VB so please forgive my retardedness.

All I want to do is simply have a macro run when the spreadsheet is printed. I have been researching the board for the past couple hours but still can't figure it out. I have seen examples of the Before_Print function in many posts but can't seem to get it to work. I've read that the code must be inserted in the "thisworkbook module" but I can't seem to find it. When I view the VB code, all I have to choose from is "general" tab or "worksheet" tab. My code is attached.

The other question I have is if there is a way to apply VB code to the whole workbook (I am assuming there is and it is probably on the elusive "thisworkbook module.") Please someone put me out of my misery.

PS: If anyone knows of a good introductory / overview of VB for idiots, please let me know. Thanks.


<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Sub Hidezero()<o></o>
For Each cell In Range("C:C")<o></o>
If cell.Value = "0" Then cell.EntireRow.Hidden = True<o></o>
Next cell<o></o>
End Sub
<o> </o>
<o> </o>

 

[VoG]

Welcome to MrExcel!

If you are using XL 2003 or before, right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call Hidezero
End Sub

then close the code window. Note that this code will fall over if you select several sheets and choose Print. If this is a possibility please post back.

 

[CHARLESJOSEPH]

Thanks VOGII. I did this and got an error message. I'm sure there is something wrong with my macro and not your code. The message is...

Compile error: sub or function not defined.

Also, is there a way I can get the VB code to apply to all sheets rather than having to program the macro into each individual sheet? I have approx 31 sheets in the workbook.

 

[VoG]

Where did you put your Hidezero macro? It needs to go in a regular module. Delete it from where you put it (I suspect in one or more Sheet modules). Press ALT + F11 to open the Visual Basic Editor then Insert > Module and paste in the Hidezero code. Close the code window using the X then try printing a sheet (or you can use PrintPreview to save wasting a rainforest whilst testing your code ).

 

[CHARLESJOSEPH]

That worked awesome! You just saved my life. THanks.

Last part - Is there a way to have it apply to all sheets?

It works only on the sheet that is active when I hit print. I want it to work on all sheets selected for print...

 

[VoG]

Try this. Modify your hide rows code to

Sub Hidezero(ws As Worksheet)
Dim cell As Range
For Each cell In ws.Range("C:C")
    If cell.Value = "0" Then cell.EntireRow.Hidden = True
Next cell
End Sub

and modify the Before_Print event code to

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
    Call Hidezero(ws)
Next ws
End Sub

 

[CHARLESJOSEPH]

Thanks VogII! You're the best!


ray:

I'm not worthy... I'm not worthy...

 

[VoG]

I assume that it worked

I'm certainly not 'the best' though I do my best. If you want to learn VBA and you are really a beginner this is great (inexpensive but not free) http://www.add-ins.com/vbhelp.htm

 

[CHARLESJOSEPH]

OK. The code works (Thanks VoGII!!!!!!!!!) but I have encountered a separate problem dealing with protection of the sheet. The spreadsheet will be used by multiple individuals across multiple areas and I am trying to make as idiot-proof as possible, hence, the sheet is protected to only allow the user to select and fill-in unprotected cells. For consistency, I have coded on each worksheet below to convert all user entries to uppercase. One question & problem as follows...

Question - how would I code this to automatically apply to all worksheets in the workbook?

Problem - when I have the spreadsheet protected and I hit print, the HideZero module fails with "Runtime error '1004' Unable to set the hidden property of the Range class." Of course this doesn't happen when the sheet is unprotected.

Any help with either of these issues would be most appreciated. - Charles


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A21:H1000")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

 

[VoG]

For the printing issue try

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
    ws.Unprotect Password:="xyz"
    Call Hidezero(ws)
    ws.Protect Password:="xyz"
Next ws
End Sub

Replace xyz with the actual password (I'm assuming the same password for each sheet or it gets complicated).

For the other issue, try removing the worksheet code and replacing with the following which goes in the ThisWorkbook module (same place as the Before_Print)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Sh.Range("A21:H1000")) Is Nothing Then
    Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub