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.

 

[Greg Truby]

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?

 

[retseort]

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

 

[Zack Barresse]

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

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

Where blnCondition is your check.

 

[retseort]

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

 

[Greg Truby]

Also, beware of "magic ranges". Better to use named ranges.

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

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.

 

[Zack Barresse]

Not sure what SetHeader is, but you can just use the variable you have to autofit the cells ...

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

 

[Greg Truby]

Using Zack's [first] example and assuming you did name F9...

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

 

[retseort]

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

 

[Greg Truby]

Oh, yeah, you probably would want to do that...

Add in a line

Cancel = TRUE

inside the IF statement somewhere before the EXIT SUB statement.