Mandatory Field on Save and Send

[Selbo33e]

I have been reading up on mandatory fields and with the help of a few forums have successfully created macros to achieve this for filling in a cell before saving or before closing.

Question is, is there a option for mandatory cell to be filled before save and send?

Or do you have to disable the save and send option?

Thanks in advance

 

[Logit]

Question is, is there a option for mandatory cell to be filled before save and send?

If you mean, does Excel have this option built-in ... the answer is no.

If this does not answer the question, please clarify.

 

[Selbo33e]

Apologies, I know that it does not have the option. The question should have been, is there a way of making a mandatory field before save and send

 

[Logit]

.
Yes.

Are you referring to a cell on the worksheet or referring to a textbox on a userform ?

 

[Selbo33e]

Referring to a Cell

 

[Logit]

.

Option Explicit




Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cells(1, 2).Value = "" Then
    MsgBox "Cell B1 requires user input", vbInformation, "Enter Data"
    Cancel = True
End If
End Sub

Paste the above in ThisWorkbook module. You will need to change the cell reference to suit.

 

[Selbo33e]

Sorry, this still allows you to use "save & send" without filling in the mandatory cell

 

[Logit]

.
Here is another macro with overkill. Confirmed it runs.

Option Explicit


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Start As Boolean
    Dim Rng1 As Range
    
    Dim Prompt As String, RngStr As String
    Dim Cell As Range
    
    Set Rng1 = Sheets("Sheet1").Range("B1")
    
    Prompt = "All cells must be completed prior to closing. "
       
    'highlights the blank cells
    For Each Cell In Rng1
        If Cell.Value = vbNullString Then
            Cell.Interior.ColorIndex = 6 '** color yellow
            If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
            Start = False
            RngStr = RngStr & Cell.Address(False, False) & ", "
        Else
            Cell.Interior.ColorIndex = 0 '** no color
        End If
    Next
      
    If RngStr <> "" Then
        MsgBox Prompt & "", vbCritical, "Incomplete Data"
        Cancel = True
    Else
        'saves the changes before closing
        ThisWorkbook.Save
        Cancel = False
    End If
  
    Set Rng1 = Nothing
    
End Sub