Workbook_BeforePrint; I can't stop it from printing
Posted by Blake Rubin on February 11, 2002 9:01 AM
i have a macro that checks a list of criteria on a worksheet before it is printed, if there are errors a userform is displayed with a list of the errors, but i cant get the cancel button on the userform to stop it from printing. does anyone know what I'm doing wrong???? Any help is appreciated, thanks.
Posted by Damon Ostrander on February 11, 2002 10:32 AM
Hi Blake,
Its hard to know what you might be doing wrong without know what you are doing. What code have you put in the BeforePrint event, and what code do you have in the userform's cancel button Click event? In your cancel button Click event you should be setting a global variable (one that is declared as Public in a macro module) to indicate you want to cancel printing. Then in your BeforePrint event you should set the Cancel argument to this variable.
Damon
Posted by Blake Rubin on February 11, 2002 11:03 AM
Damon,
i tried what you suggested, the assumtions you made were correct, but once i get out side of the beforeprint macro nothing seems to stop it from printing, im not sure if its because its a "private sub" instead of "sub" proceedure. When i made cancel a global variable it didnt work. the code kinda looks like this
public cancel
Private Sub Workbook_BeforePrint(cancel As Boolean)
'there are a bunch of if statements
userform1.show
end sub
Private Sub CommandButton2_Click() 'which is the cancel button
userform1.hide
cancel=true
end sub
thanks again for your help.
Blake
Posted by Jim on February 11, 2002 6:49 PM
Hi Blake,
Private Sub Workbook_BeforePrint(Cancel As Boolean)
"your code & if your if statements find an error"
Cancel = True
End Sub
Jim ,
Posted by Damon Ostrander on February 12, 2002 12:22 PM
Blake,
Yes, that information helps. Here's what you should do:
1. Add the statment
Public CancelPrint As Boolean
to the top of a macro module (NOT part of the userform or workbook event code). This makes this variable visible to both the userform code and the BeforePrint event code.
2. Set the CancelPrint variable in the CommandButton2 Click event:
Private Sub CommandButton2_Click() 'which is the cancel button
unload userform1
CancelPrint=true
end sub
3. Setup your BeforePrint event:
Private Sub Workbook_BeforePrint(cancel As Boolean)
'there are a bunch of if statements
CancelPrint = False
'will set CancelPrint to True if user clicks Cancel Print button
userform1.show
'Set cancel True or False depending on value of CencelPrint
Cancel = CancelPrint
end sub
Note that you don't have to Hide the userform to make the cancel work--so I recomment simply unloading it since the CancelPrint variable does the work. That way each time you do a print the userform would re-load.
Happy computing.
Damon
