Befor Close not responding

[L. Howard]

With this in the ThisWorkbook module, workbook saved as .xlsm, does not respond to the 200 CountA query.

When run as a non event macro and I get the msgbox alert just fine.

Howard

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Sub zzz()
With Sheets("ABC")
   If WorksheetFunction.CountA(Sheets("ABC").Range("A1:A200")) <> 200 Then
   
      Dim Reply As VbMsgBoxResult
      Reply = MsgBox("Cells A1 to A200" _
             & vbCr & "must have values" _
             & vbCr & "before the workbook is closed", _
             vbOKOnly + vbExclamation, "Two Hundred Empty!")
   
      Exit Sub
      Else
   End If
   
End With
End Sub

 

[L. Howard]

Tried Cancel = True also, but ... no go.

Howard

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Sheets("ABC")
   If WorksheetFunction.CountA(Sheets("ABC").Range("A1:A200")) <> 200 Then
   
      Dim Reply As VbMsgBoxResult
      Reply = MsgBox("Cells A1 to A200" _
             & vbCr & "must have values" _
             & vbCr & "before the workbook is closed", _
             vbOKOnly + vbExclamation, "Two Hundred Empty!")
             
             Cancel = True
             Exit Sub
      Else
   End If
   
End With

End Sub