VBA & Macro Problems

How to Show a VBA Message Box Before Closing Excel If a Cell Is Greater Than Zero

Tauseeq MagsiTauseeq Magsi Nov 3, 2026 871 views

Question details

The user wants to display an OK/Cancel warning message box before closing an Excel workbook, triggered only when cell A1 on Sheet8 contains a value greater than zero.

How to Show a VBA Warning Message Box Before Closing Excel If a Cell > 0
Product
Excel
Device & OS
not provided
Scenario
Preventing accidental closure of an Excel workbook when specific data conditions (e.g., outstanding values greater than zero) are met.
Observed behavior
The user needs a script that successfully displays a yellow warning icon and OK/Cancel buttons. Selecting 'Cancel' must abort the workbook closure.
Before you start

Ensure you have the Developer tab enabled in your Excel ribbon and macro settings configured to allow running VBA code.

Solution 1Recommended

Use the Workbook_BeforeClose VBA Event

Implement the Workbook_BeforeClose event in the ThisWorkbook module to check a specific cell's value and intercept the close action with a MsgBox.

To intercept the closing of a workbook, the VBA code must be placed specifically inside the 'ThisWorkbook' module rather than a standard module. By utilizing the 'Cancel = True' command within an 'If' statement, you can stop the workbook from closing if the user clicks 'Cancel' on the message box.

The 'vbOKCancel + vbExclamation' parameter adds both the OK/Cancel buttons and the standard yellow warning triangle to your message box.

1
Open the VBA Editor

Press 'Alt + F11' on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Access the ThisWorkbook Module

In the Project Explorer pane on the left side, locate your workbook name and double-click on 'ThisWorkbook'.

3
Insert the VBA Code

Copy and paste the following code into the right-hand code window: Private Sub Workbook_BeforeClose(Cancel As Boolean) If Sheets("Sheet8").Range("A1").Value > 0 Then If MsgBox("WARNING! There are still Fuel Cards outstanding!", vbOKCancel + vbExclamation) = vbCancel Then Cancel = True End If End If End Sub

4
Save as a Macro-Enabled Workbook

Go to 'File' > 'Save As' and ensure you save the file format as an 'Excel Macro-Enabled Workbook (*.xlsm)' so the code is preserved.

Use the Workbook_BeforeClose VBA Event
Code Customization: You can easily customize the sheet name ('Sheet8') and cell reference ('A1') in the code to match the exact location of your data.
Advanced Macros in WPS Office

Use WPS Spreadsheet for Seamless Macro Execution

WPS Office fully supports VBA macros, making it easy to run, edit, and implement complex logic like conditional Workbook_BeforeClose events without altering your workflow.

  1. 1. Open your macro file in WPS Spreadsheet: Launch WPS Office and open your .xlsm file containing the Workbook_BeforeClose macro.
  2. 2. Access the Developer Tools: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor' to view or edit your macro scripts.
  3. 3. Run your macros naturally: Save and close the workbook. The VBA code logic works perfectly in WPS Spreadsheet, prompting you correctly before closing.
Lightweight, fast-loading, and completely free to download.High format compatibility with Microsoft Excel (.xlsx, .xlsm, .xls).Built-in VBA editor to seamlessly create, edit, and troubleshoot macros.Familiar user interface requiring zero learning curve.
microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my Workbook_BeforeClose macro running when I close the file?

There are three common reasons: macros are disabled in your Trust Center settings, the code was accidentally placed in a standard Module instead of the 'ThisWorkbook' module, or 'Application.EnableEvents' was set to False by another script.

How do I change the text inside the VBA message box?

Locate the MsgBox line in the VBA code and change the text inside the quotation marks. For example, replace 'WARNING! There are still Fuel Cards outstanding!' with your preferred alert message.

Can I save this macro in a standard .xlsx file?

No. Standard .xlsx files cannot store VBA macro code. You must save the file as an Excel Macro-Enabled Workbook (.xlsm) or an Excel Binary Workbook (.xlsb) to retain the code upon closing.

How can I check multiple cells instead of just A1?

You can modify the 'If' statement to use 'Application.WorksheetFunction.Sum(Sheets("Sheet8").Range("A1:A10")) > 0' to check if the total of a range is greater than zero, or use 'Or' operators for specific individual cells.