Forum Discussion

Viorica000's avatar
Viorica000
Copper Contributor
Jun 27, 2023

Don't save sheet if condition not met (If cell is highlight)

Hello everyone,

I wanted to ask if you can set a rule with VBA where you won’t allow the end user to save the file if condition not met in this case if is a highlighted cell inside one range. 

Would anyone have a sample code?

Thanks for the help

excel
Formulas and Functions
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jun 28, 2023

    Viorica000 

    Here's an example code that demonstrates how you can achieve this:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Check if any highlighted cells are present
    If HighlightedCellsExist() Then
        MsgBox "Please remove the highlighted cells before closing the workbook.", vbExclamation, "Cannot Close"
        Cancel = True ' Cancel the close operation
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' Check if any highlighted cells are present
    If HighlightedCellsExist() Then
        MsgBox "Please remove the highlighted cells before saving the workbook.", vbExclamation, "Cannot Save"
        Cancel = True ' Cancel the save operation
    End If
End Sub

Private Function HighlightedCellsExist() As Boolean
    Dim rng As Range
    Set rng = Sheet1.Range("A1:A10") ' Specify the range where you want to check for highlighted cells
    
    ' Check if any cell within the range is highlighted
    HighlightedCellsExist = WorksheetFunction.CountA(rng.SpecialCells(xlCellTypeConstants, xlLogical)) > 0
End Function

    Now, when the user tries to close the workbook or save it while there are highlighted cells within the specified range, a message box will appear, and the close or save operation will be canceled until the highlighted cells are cleared.

Resources