VBA Mandatory fields based on cell value - locking save until fields are filled out

[RH2448]

Hi all,

Attempting something a bit above my skill level.

I am attempting to set up a sheet that requires mandatory cells to be filled out before the worksheet can be saved.
The twist is I only need these mandatory cells to be filled out if another cell has an input value of "YES".

My example is if cell B68 has a text value "YES" input into it, cells B69, B70, B71 and B72 require text values input into them before the worksheet can be saved.
If B68 has any other or no text value input, mandatory cells don't require filling out in order to save the worksheet.

Hoping someone can help me out with the VBA coding required

 

[Joe4]

Welcome to the Board!

I think the best way to do this would probably be with a "Workbook_BeforeSave" event procedure in VBA.
Basically, you can program it to do data validation whenever anyone tries to save the file. If it does not meet the requirements, then it will not allow them to save the file.
Here is an example here: validation before exit or save

The only caveat is that the user MUST enable VBA code/Macros to run on their workbook in order for the VBA code to fire.
What some users will do is "hide" the data/worksheets by default, and have a "Workbook_Open" event procedure code that unhides the data when they open the workbook.
If they do not enable VBA, that code will not run, and they will not see the data.

If you are interested in using this VBA approach, please let us know what the name of this worksheet that you want to apply it to is named, and if there are any other worksheets in the workbook.

 

[RH2448]

Thanks for the reply.

Tried the coding in the link but think my understanding of how it works is still not there.

Name of the work sheet i am using is "WorkOrder"

 

[Joe4]

Here is one way to write that code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim ws As Worksheet
    Dim rng As Range
    
    Set ws = Sheets("WorkOrder")
    Set rng = ws.Range("B69:B72")
    
    If ws.Range("B68").Value = "YES" Then
        If Application.WorksheetFunction.CountA(rng) < 4 Then
            Cancel = True
            MsgBox "Cannot save until cells B69:B72 are populated", vbOKOnly, "SAVE FAILED!"
        End If
    End If

End Sub