MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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