Custom Printing Function from the Printing Icon

[IAIPICI]

Hello,

I am trying to create a macro which will hide sheets before a sheet is printed (depending on msg box answer) after pressing the excel print button.

I have viewed many other threads and have made some progress, however still have problems.

Senario

I have 3 rows (1,2 & 3). Apon pressing the print button I have a vbYesNoCancel box which asks the user if they wish to hide row 2 before printing. Now the code does work however not only does the msg box display twice, It also prints twice. The first page comes out exactly how I want it (with the hidden row). But the second page (which I do not want printing at all) comes out with all rows displayed.

Please see my code below:

<CODE>
Sub Workbook_BeforePrint(Cancel As Boolean)
'Cancel = False
Print_OPT
'Cancel = True
'NOTE: If I use the above "Cancel = True" to attempt to stop the standard printing, my custom print macro wont work either. If I dont use it, as said above I get prompted by the msg box twice and two copies are printed (one with the hidden row and one without)
End Sub
Sub Print1()
ActiveSheet.Unprotect Password:="password"

Rows("2").EntireRow.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Rows("2").EntireRow.Hidden = False
Range("A1").Select

ActiveSheet.Protect Password:="password"
End Sub
Sub Print2()
ActiveSheet.Unprotect Password:="password"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveSheet.Protect Password:="password"
End Sub

Sub Print_OPT()


Dim iQuestion As Byte, iType As Integer
iType = vbYesNoCancel + vbInformation + vbDefaultButton3
iQuestion = MsgBox("Would you like to Hide row 2?", iType)
If iQuestion = vbYes Then
Print1
ElseIf iQuestion = vbNo Then
Print2

Else
Exit Sub
End If
End Sub

</CODE>

Any help would be very much appreciated

Thanks,

 

[IAIPICI]

In addition:

I am using windows XP, and Excel 2003.

Hope someone can help