Determining if Save, Dont' Save or Cancel is Used in VBA

Question

I am using Workbook_BeforeSave event to check whether all the mandatory cells are filled before saving the document. If there are any mandatory cells that are empty, it will ask the user to fill them before saving the document.

The code is working as expected. But the only issue I am facing is that when user clicks Close(X) without filling the mandatory cells, excel is prompting whether the user wants to Save / Don't Save the changes before closing the workbook or Cancel the prompt and if the user clicks Save, Workbook_BeforeSave event is called and it informs the users that there are empty mandatory cells to be filled and closes the document abruptly.

I don't want to close the workbook when the user clicks Save while there are mandatory cells to be filled. If he clicks Don't Save its ok close the document without filling the mandatory cells.

How to do this.

Code,

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wb As Workbook
Dim ws1 As Worksheet
Dim userange, userange1, userange2 As Range
Dim iCell, positionrng As Range
Dim usedrow As Long
Dim usecolumn As Long
Dim rowposition As Long
Dim ws1lastrow, lastcol As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("CAPEX FY21-22")

rowposition = 9
criteria = ws1.Range("BM9").Value
investment = ws1.Range("BT9").Value
roi = ws1.Range("BU9").Value
justification = ws1.Range("BW9").Value

ws1lastrow = ws1.Cells(Rows.Count, criteria).End(xlUp).Row

If ws1.Cells(rowposition, criteria).Value = "" Or ws1.Cells(rowposition, criteria).Value = Empty Then
GoTo exiting:
End If

Set userange1 = ws1.Range(ws1.Cells(rowposition, criteria), ws1.Cells(ws1lastrow, investment))
Set userange2 = ws1.Range(ws1.Cells(rowposition, roi), ws1.Cells(ws1lastrow, justification))
Set userange = Union(userange1, userange2)


For Each iCell In userange

If IsEmpty(iCell) = True Then

MsgBox ("Document cannot be saved!" & vbCrLf & "Mandatory cell(s) are empty!" & vbCrLf & "Please fill the highlighted cell to save.")

iCell.Activate

ActiveCell.Interior.Color = RGB(255, 255, 0)

Application.Goto ActiveCell, Scroll:=True

Cancel = True

Exit Sub

GoTo exiting:

End If

Next


saves:

MsgBox ("Document saved")

exiting:
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Answer 1

There are two events that are relevant to your plan. One is the Workbook_Close event which is triggered by clicking on the X or, in fact, a number of other user actions. The other is the Workbook_Save event which is triggered by any user action that causes the workbook to be saved, including by SaveAs.

You have provided for one of these events but not for the other. Observe that the Close action will normally (unless suppressed) cause Excel to to check the Saved property and ask the user if he wants to save, if the workbook isn't clean. If the user chooses to save the `Workbook_Save~ event will fire which would trigger your procedure.

To take better control of these events you should consider having an even procedure for each of these events, perhaps like this:-

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    With ThisWorkbook
        If Not .Saved Then
            Cancel = NotDone
            If Not Cancel Then
                .Save
            End If
        End If
    End With
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = NotDone
End Sub

Private Function NotDone() As Boolean
    ' return True if mandatory cells aren't all filled
    
    ' =================================================
    ' basically, your existing code goes here
    ' =================================================
End Function

In this scenario your existing code goes into the function NotDone nearly unchanged but made to return True if mandatory fields weren't filled (otherwise False). If the user attempts to close the workbook the Before_Close procedure checks if the workbook is already saved and runs NotDone to check if all cells were filled if it isn't. If nothing amiss is found the procedure quietly saves the workbook and then closes it. Else the action is cancelled (There is a message to this effect in the function NotDone.

On the other hand, if the user just tries to save the event procedure's Cancel property is set to the value returned by NotDone, thereby either permitting the save action to proceed or cancelling it.

I tried to help you with the function but found your code not sufficiently transparent. Note that Dim userange, userange1, userange2 As Range makes only the last item of Range datatype, the others as variants. You must assign a datatype to each listed variable individually. It's not good practice to have rowposition = 9 followed by criteria = Ws1.Range("BM9").Value. Use either the variable or the hard code but not both. There isn't a proper reason to suppress alerts, screen updating or application events. Doing so may be without effect or adverse effect but never good because no effect is targeted.

Answer 2

I suggest you restructure your code. Your Workbook_BeforeSave is doing too many things. Think about how you can divide the responsibilities between different subs and functions. I say this because most of the logic you have in Workbook_BeforeSave is needed for Workbook_BeforeClose event handler.

I suggest the following structure:

Write a function that will return whether or not it is ok to save the workbook. This function will have almost all your current code. You can save this in a standard module

Function IsOKToSave() As Boolean
    ' Returns True if all mandatory cells are filled
    ' Otherwise, returns False
End Function

Use this function in your event handlers.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim iResponse As Integer
        
    ' Handle the saving yourself
    ' first check if the workbook has changed since it was last saved
    If Not Me.Saved Then
        If IsOkToSave Then
            iResponse = MsgBox("Do you want to save changes to '" & Me.Name & "'?", vbYesNoCancel)
            If iResponse = vbYes Then
                Application.EnableEvents = False
                Me.Save
                Application.EnableEvents = True
            ElseIf iResponse = vbCancel Then
                Cancel = True
            Else
                ' close without saving
                Me.Saved = True
            End If
        Else
            iResponse = MsgBox("Document cannot be saved!" & vbCrLf & _
                               "Mandatory cell(s) are empty!" & vbCrLf & vbCrLf & _
                               "Do you want to close WITHOUT saving?", vbYesNo + vbDefaultButton2)
            If iResponse = vbYes Then
                ' close without saving
                Me.Saved = True
            Else
                ' Cancel closing the workbook
                Cancel = True
            End If
        End If
    End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not IsOkToSave Then
        Cancel = True
        MsgBox "Document cannot be saved!" & vbCrLf & _
               "Mandatory cell(s) are empty!" & vbCrLf & _
               "Please fill the highlighted cell to save."
    End If
End Sub

Also note that you need to declare the type of each variable. Therefore, the line

Dim userange, userange1, userange2 As Range

should be

Dim userange As Range, userange1 As Range, userange2 As Range

Othewise, userange and userange1 will be of type Variant

Answer 3

While workbook is closing, an event BeforeClose gets called. The solution might be to put your validation logic there, and prevent workbook from closing when validation didn’t succeed.

Private Sub Workbook_BeforeClose(Cancel as Boolean)
    If IsValid = False Then
       Cancel = True
    End If
End Sub

Private Function IsValid() As Boolean
    ' Validation code goes here.
End Function