How to set mandatory fields in spreadsheet

[minesh]

Hi All,

How can we set mandatory fields in excel, so that i cannot be saved unless fields are filled .

Many Thanks in advance

Regards
Minesh

 

[Deepak]

This only possible with VBA. Are u comfortable for the same.

 

[Chihiro]

This can be done through VBA.

Code in "ThisWorkbook", BeforeSave

Dim mCell As String
mCell = "B2"
    If Sheet1.Range(mCell).Value = "" Then
        MsgBox "Please enter value in " & mCell
        Cancel = True
        Exit Sub
    End If
End Sub

You can have as many IF statements as needed and make sure to have mCell defined before each IF statement.

 

[minesh]

Thanks a lot Chihiro.

Its workings but how to set up multiple if statements. Because i get error

 

[Chihiro]

Just repeat everything from mCell = "" to End If

Like so.

Dim mCell As String
mCell = "B2"
    If Sheet1.Range(mCell).Value = "" Then
        MsgBox "Please enter value in " & mCell
        Cancel = True
        Exit Sub
    End If
mCell = "C2"
    If Sheet1.Range(mCell).Value = "" Then
        MsgBox "Please enter value in " & mCell
        Cancel = True
        Exit Sub
    End If
End Sub

There's more elegant way to do it. But this will do what's needed.

 

[Marc L]

Hi !​

There's more elegant way to do it. But this will do what's needed.

For C& = 2 To 3
    With Sheet1.Cells(2, C)
        If .Value = "" Then
           Cancel = True
           MsgBox "Please enter value in " & .Address(0, 0)
           Exit For
        End If
    End With
Next

Do you like it ? So thanks to click on bottom right Like !
​

 

[Deepak]

Something like

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Range(Join$(Filter(Evaluate("IF(LEN(A1:D1)=0,ADDRESS(1,COLUMN(A1:D1)))"), False, False), ","))
    If .Cells.Count > 0 Then MsgBox .Address & " Found blank", vbInformation: Cancel = True
End With

End Sub

 

[Deepak]

Slightly modified!!

Dim varCell As Variant, str As String

str = "IF(LEN(A1:D1)=0,ADDRESS(1,COLUMN(A1:D1)))"

varCell = Filter(Evaluate(str), False, False)

If Not UBound(varCell) = -1 Then MsgBox Range(Join(varCell, ",")).Address & " Found blank", vbInformation: Cancel = True

 

[Chihiro]

Hi Deepak,

Nice. I like the use of LEN.

 

[Marc L]

My Deepak's revisited ! ​

S$ = Join$(Filter(Evaluate("IF(A1:D1="""",ADDRESS(1,COLUMN(A1:D1),4))"), False, False), " & ")
If S > "" Then Cancel = True: MsgBox "Please enter value in " & S, vbExclamation, "Can't save !"

 

[Chihiro]

Good stuff Deepak & Marc L. Thanks for improving the code.