Q:

Macro enabled workbook not running

I'm trying to ensure that for each row in my spreadsheet, if cell B or C is not populated (in the range), then a message box alerts the user - and doesn't allow it to be saved.
I have the workbook saved as a Macro enabled (XLSM) file - but the Workbook_BeforeSave doesn't appear to be triggering.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim rng1 As Range
Dim rng2 As Range
MsgBox "hi"
Set rng1 = ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(3, 4)).Select
Stop
 
'turn off events to avoid code retriggering itself
Application.EnableEvents = False
For Each rng2 In rng1
    If Cells(rng2.Row, "B") = vbNullString Then
       MsgBox "Please Enter Something in Cell B - your entry will be deleted", vbOKOnly
       rng2.Value = vbNullString
    End If
    If Cells(rng2.Row, "C") = vbNullString Then
       MsgBox "Please Enter Something in Cell C - your entry will be deleted", vbOKOnly
       rng2.Value = vbNullString
    End If
    
Next
Application.EnableEvents = True
End Sub

Can anyone see where I may have gone wrong? Macros are enabled for the workbook.
Thanks for any advice,
Mark

A:

Somehow in your code, you have disabled the events, thus the Save event is not caught. Try the following:

Press Ctrl + G, while you are selecting the Visual Basic Editor;
Write Application.EnableEvents = True on the Immediate window that shows up;
Press Enter;

Now the events would be activated.

As mentioned by Jeep, using error handling is a good idea in this case, thus the events are always enabled back if something "bad" happens during code execution:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    On Error GoTo Workbook_BeforeSave_Error

    'code here        

    On Error GoTo 0
    Exit Sub

Workbook_BeforeSave_Error:
    Application.EnableEvents = True
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Workbook_BeforeSave"
End Sub

