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,
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,