VBA code checks a condition, stops macro if FALSE, etc.

[retseort]

The following code is found in ThisWorkbook.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Application.ScreenUpdating = False

For Each wkSht In ThisWorkbook.Worksheets

SetHeader wkSht

Next wkSht
Application.ScreenUpdating = True
End Sub

Before this kicks off I need it to check a condition. I.e. cell F9 is less than 232

If true it proceeds with the SetHeader wkSht code or jsut continues the macro.

If False it stops the macro and gives a message that they must reduce the # of chars to less than 232. They click ok and it retunrs them back to worksheet titled headerpage.

Any help is appreciated.

 

[retseort]

YEAHHHHHHHHHH It worked...you ROCK thank you both!!!!!!!


:wink:

 

[retseort]

Hey Greg

Ok new day, and I got a better chance to view this.

It works great except at this point...

Sheets("headerpage").Activate
Cancel = True

It does activate the headerpage sheet. However the hour glass shows in place of the mouse pointer as if the macro is still running. When I click on other cells it does not activate the cells even though it moves the cell box. If I click on another worksheet then go back to the header page it stops the issue.

It would seem the Cancel=True is not really ending the macro.

One more thing, this probelm only occurs if I am on another worksheet. If I am on the headerpage it works fine.

Thoughts?

 

[Zack Barresse]

It does not end the macro, it just tells the Cancel variable in the Print Routine that the value is TRUE, which means no printing. To end the routine, that's what END SUB does.

       '...
       Sheets("headerpage").Activate
       Cancel = True
       Exit Sub
       '...

That says, activate the sheet specified, cancel the printing, exit this routine. Try putting your cursor on the line Sheets("headerpage").Activate and hitting F9, this will toggle a breakpoint in it by highlighting the line red. You can toggle this on/off by F9 or clicking with your mouse where the red dot is on the very left of the highlighted area. Now, when you run your routine, it will break on that point and bring up your Visual Basic Editor (VBE). From there, you can step through your code with F8.

HTH