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

retseort

New Member
Joined
Oct 31, 2005
Messages
34
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
You cannot halt a macro and allow for editing of cells and then resume a macro. The best you could do would be to show a userform with the text that needs editing; have them edit in the userform and close the form and then continue with the macro. Or perhaps I misunderstand your question?
 
Upvote 0
Greg, thanks for the response.

I do not want the macro to stop - let me edit - then resume. I would just want it to stop if the condition 'cell F9 is less than 232' is False.

If the condition is False it should stop the macro and take them to the worksheet titled 'headerpage'

Thanks
Dan
 
Upvote 0
Well, you can make it stop to edit cells, actually. By using the Stop command, it will be stopped in debug mode, but you can edit cells and resume again with F5.

As far as your code goes ...

Code:
If Not blnCondition Then
    Sheets("headerpage").Activate
    Exit Sub
End If

Where blnCondition is your check.
 
Upvote 0
Where would I enter that code? I placed it in there and it did some funky things.

I am not sure I want it in debug mode. But I would like to see your code work first before I determine this is not what I am looking for. The end users of this may or may not have much excel understanding.

Here is my code...where does your fit in?

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


Thanks
Dan
 
Upvote 0
Also, beware of "magic ranges". Better to use named ranges.
Code:
If Range("F9") > 232 Then
   Msgbox "Edit the header ya idjit."
End If
will have the wheels fall off if you insert/delete rows/columns above/left of F9. Better to name F9 something clever like HeaderLen and code
Code:
If Range("HeaderLen") > 232 Then
   Msgbox "I so hate to complain, but could you please edit the header a bit."
End If
That way if it moves, your code doesn't screech to a halt.
 
Upvote 0
Not sure what SetHeader is, but you can just use the variable you have to autofit the cells ...

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

For Each wkSht In ThisWorkbook.Worksheets

SetHeader wkSht
wkSht.Cells.EntireColumn.Autofit

Next wkSht
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Using Zack's [first] example and assuming you did name F9...
Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 

   Const c_intMaxHdrLen as Integer = 232

   Dim wkSht As Worksheet 

   If Range("HeaderLen") > c_intMaxHdrLen Then 
       Msgbox "I so hate to complain, but could you please edit the header a bit." 
       Sheets("headerpage").Activate
       Exit Sub 
   End If 

   Application.ScreenUpdating = False 
   For Each wkSht In ThisWorkbook.Worksheets 
      SetHeader wkSht 
   Next wkSht 
   Application.ScreenUpdating = True 
End Sub
 
Upvote 0
Thanks Zack and Greg.

HOWEVER, Greg,

I entered your code you provided in the last message and it worked. Except it did not stop the macro. Since this is placed in the beforeprint event I would need it to stop the print process. In this case it doesn't. Yes it does pop up the Msgbox "I so hate to complain, but could you please edit the header a bit."

But it just continues the macro after I click ok. I need the macro to stop entirely so the user can edit the headerpage worksheet.

Then when they go to print it again the macro is restarted at the beginning.

Thanks
 
Upvote 0
:lol: Oh, yeah, you probably would want to do that...

Add in a line
Code:
Cancel = TRUE
inside the IF statement somewhere before the EXIT SUB statement.
 
Upvote 0

Forum statistics

Threads
1,224,266
Messages
6,177,541
Members
452,782
Latest member
ZCapitao

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top