Macro enabled workbook not running

Question

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

This is because you have Application.EnableEvents = False. — Vityata
Is it in the correct workbook? Select Workbook then BeforeSave from the two drop-downs at the top of your screenshot. If this doesn't select your code, but creates an empty procedure stub, then move your code into this stub. — Andy G

Vityata Vityata · Accepted Answer · 2018-02-23T11:29:18

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

Macro enabled workbook not running

1 Answers