Prevent Workbook from saving until certain cells are completed

leonielroux

New Member
Joined
Dec 18, 2023
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
1702910757674.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Does this work for you?


VBA Code:
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
 
Upvote 0
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:
VBA Code:
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:
VBA Code:
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:

VBA Code:
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
 
Upvote 0
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 :)
1702966157990.png

1702966181643.png
 
Upvote 0
What sets "Are_Mandatory_Cells_Completed"?
 
Upvote 0
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:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top