Prevent Workbook from saving until certain cells are completed

[leonielroux]

Good day,

I have a worksheet that have mandatory fields across multiple sheets that I need completed. However, these mandatory fields only become mandatory if they complete Column A, which is like an employee code.

I also have mandatory fields that are dropdowns, but need to be selected (once again, only becomes mandatory if the employee code in Column A is entered).

I have tried the Before Save Event, but then I keep getting the error message that column A is not completed for the empty rows.

How do I prevent the workbook from saving unless the fields with Column A completed, are filled it?

Below is a screenshot of my layout. All the green highlighted columns are mandatory, once the employee code is completed.

 

[nemmi69]

Does this work for you?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim PassChk As Boolean
Dim LastRow As Long
Dim MsgTxt As String

LastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
PassChk = False

With Sheets("sheet1")
    If IsEmpty(Range("D" & LastRow)) Then
        MsgTxt = "Action Type is missing"
    ElseIf IsEmpty(Range("F" & LastRow)) Then
        MsgTxt = "Component is missing"
    ElseIf IsEmpty(Range("H" & LastRow)) Then
        MsgTxt = "Input Value is missing"
    Else
        PassChk = True
    End If
End With
If PassChk = False Then
    MsgBox MsgTxt, vbCritical, thisworkbook.Name
    Cancel = True
End If
End Sub

 

[John_w]

I have a worksheet that have mandatory fields across multiple sheets that I need completed. However, these mandatory fields only become mandatory if they complete Column A, which is like an employee code.

How do I prevent the workbook from saving unless the fields with Column A completed, are filled it?

Add this code to a standard module:

Public Function Are_Mandatory_Cells_Completed() As Boolean

    Dim ws As Worksheet
    Dim lastRow As Long, r As Long
    Dim numFilled As Long
   
    Are_Mandatory_Cells_Completed = True
   
    For Each ws In ThisWorkbook.Worksheets
        With ws
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For r = 2 To lastRow
                If Not IsEmpty(.Cells(r, "A").Value) Then
                    numFilled = Application.WorksheetFunction.CountA(.Cells(r, "D"), .Cells(r, "F"), .Cells(r, "H"))
                    If numFilled <> 3 Then
                        Are_Mandatory_Cells_Completed = False
                        Exit For
                    End If
                End If
            Next
         End With
    Next
   
End Function

And add this code to the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
    If Are_Mandatory_Cells_Completed = False Then
        Cancel = True
        MsgBox "Workbook not saved because not all mandatory cells have been completed.", vbExclamation, "Save Workbook"
    End If
   
End Sub

Do you also want to prevent the workbook from closing if not all the mandatory cells have been completed? If so, you could use the Workbook_BeforeClose event handler like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   
    Dim resp As Variant
   
    If ThisWorkbook.Saved = False Then
        If Mandatory_Cells_Are_Completed = False Then
            resp = MsgBox("Changes have been made to the workbook and not all mandatory cells have been completed.  Do you want close the workbook?", vbYesNo + vbExclamation, "Close Workbook")
            If resp = vbNo Then
                Cancel = True
            End If
        End If
    End If

End Sub

 

[leonielroux]

Hi @nemmi69 , unfortunately yours does not work.

Thank you @John_w , yours is working, however, I persistently get the message that I have not completed all the mandatory fields, even though I have. Please help

 

[nemmi69]

What sets "Are_Mandatory_Cells_Completed"?

 

[John_w]

Thank you @John_w , yours is working, however, I persistently get the message that I have not completed all the mandatory fields, even though I have. Please help

In your OP, you said the mandatory fields are across multiple sheets. The For Each ws In ThisWorkbook.Worksheets in the code means it is checking every worksheet.

If you want to check only specific worksheets, for example "Once_Off_Input" and "Recurring_Input", change the function to:

Public Function Are_Mandatory_Cells_Completed() As Boolean

    Dim wsName As Variant
    Dim lastRow As Long, r As Long
    Dim numFilled As Long

    Are_Mandatory_Cells_Completed = True

    For Each wsName In Array("Once_Off_Input", "Recurring_Input")
        With ThisWorkbook.Worksheets(wsName)
            lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For r = 2 To lastRow
                If Not IsEmpty(.Cells(r, "A").Value) Then
                    numFilled = Application.WorksheetFunction.CountA(.Cells(r, "D"), .Cells(r, "F"), .Cells(r, "H"))
                    If numFilled <> 3 Then
                        Are_Mandatory_Cells_Completed = False
                        Exit For
                    End If
                End If
            Next
         End With
    Next

End Function