Prevent Excel from Saving When Cells Are Highlighted Using VBA in WPS Office
Question details
The user needs a VBA macro that stops a workbook from being saved or closed while highlighted cells exist in a specified worksheet range.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages a workbook in which highlighted cells in a specific range signal incomplete or invalid entries and wants to block saving or closing until those highlights are cleared.
- Observed behavior
- By default, the workbook can still be saved or closed when highlighted cells are present. The goal is for the Workbook_BeforeSave and Workbook_BeforeClose events to detect highlighted cells and cancel the action with a prompt.
Open the workbook in WPS Office Spreadsheets, confirm that you can access the macro editor by pressing Alt+F11, and identify the exact worksheet name and range to check for highlighted cells before you add the code.
Add Workbook_BeforeSave and Workbook_BeforeClose VBA events in the ThisWorkbook module
Use this solution when you want WPS Office Spreadsheets to cancel Save and Close actions automatically while a highlighted cell remains in the checked range.
The macro uses the Workbook_BeforeSave and Workbook_BeforeClose events, which fire before the workbook is saved or closed. A helper function loops through the specified range and returns True when a cell has any fill color, which then cancels the operation.
Use this code in the ThisWorkbook module: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If HighlightedCellsExist() Then MsgBox "Please remove the highlighted cells before saving the workbook.", vbExclamation, "Cannot Save" Cancel = True End If End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) If HighlightedCellsExist() Then MsgBox "Please remove the highlighted cells before closing the workbook.", vbExclamation, "Cannot Close" Cancel = True End If End Sub Private Function HighlightedCellsExist() As Boolean Dim cell As Range For Each cell In Sheet1.Range("A1:A10") If cell.Interior.ColorIndex <> xlColorIndexNone Then HighlightedCellsExist = True Exit Function End If Next cell End Function
In WPS Office Spreadsheets, open the workbook and press Alt+F11 to open the VBA editor.
In the Project Explorer pane, find the current workbook project, expand Microsoft Excel Objects, and double-click ThisWorkbook.
Paste the provided code into the ThisWorkbook module. Change Sheet1 and A1:A10 in the HighlightedCellsExist function to the worksheet name and range you need to check.
Close the editor and save the file as a WPS Office macro-enabled workbook format such as .xlsm so the VBA code remains available.
Block Excel from Saving While Highlighted Cells Remain with WPS Office VBA
Add Workbook_BeforeSave and Workbook_BeforeClose VBA procedures in WPS Office Spreadsheets to cancel saving or closing when highlighted cells are left in a specified range. WPS Office supports the familiar VBA event model, so you can keep the same Excel macro workflow in a free, lightweight Office suite.
- 1. Open your workbook and launch the macro editor: In WPS Office Spreadsheets, open the target workbook and press Alt+F11 to open the VBA editor.
- 2. Add the event code to ThisWorkbook: Double-click ThisWorkbook in Project Explorer, paste the BeforeSave and BeforeClose event procedures, and adjust the worksheet and range in HighlightedCellsExist.
- 3. Save in a macro-enabled format: Save the file as .xlsm so the VBA code is retained and runs the next time the workbook is opened.
- 4. Test the save and close guard: Highlight a cell in the specified range, then try to save or close. A prompt should appear and the action should be cancelled until the highlight is cleared.
Frequently Asked Questions
Does this VBA code work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the Workbook_BeforeSave and Workbook_BeforeClose events in the ThisWorkbook module. Press Alt+F11, add the code, and save the file as a macro-enabled workbook so the events can run.
How do I change the worksheet and range in the macro?
In the HighlightedCellsExist function, replace Sheet1 with your worksheet name and A1:A10 with the range you want to check. For example, use Sheet2.Range("B2:B50") to check B2:B50 on Sheet2.
Why is the macro not running after I save the workbook?
The file must be saved in a macro-enabled format such as .xlsm. If macros are disabled, enable them in the WPS Office macro security settings, then reopen the workbook and test again.
Can I check for only one specific highlight color instead of any fill color?
Yes. Modify the condition in HighlightedCellsExist to compare the cell's Interior.Color to a specific color, such as RGB(255, 255, 0) for yellow, instead of testing ColorIndex <> xlColorIndexNone.
What should I do if I cannot find the Developer tab in WPS Office Spreadsheets?
You do not need the Developer tab to open the VBA editor; press Alt+F11. If you want the Developer tab, add it through the ribbon customization or WPS Office settings.
