Mandatory fields in user form.

Posted by David Megnin on April 05, 2001 1:18 PM

I need to make fields in a user form (cells) mandatory. Users tend to leave required fields blank.
Validation does not prevent them from leaving cells blank and I need to enforce entries in all mandatory fields. Thanks

Posted by David Hawley on April 05, 2001 4:32 PM

Hi Dave

You will need VBA to achieve this, try the code below in the Sheet module.

Option Explicit
Dim Mandatory As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub

Select Case Target.Address
Case "$A$5", "$A$10", "$A$15", "$A$20", "$A$25", "$A$30"
Set Mandatory = Target
Case Else
If Not Mandatory Is Nothing Then
If Mandatory = "" Then
Mandatory.Select
MsgBox "You cannot leave this cell blank"
End If
End If
End Select

End Sub

To access the sheet module, right click on the sheet name tab and select "View Code". Change the cell addresses to suit. Push Alt+Q to return to Excel.

Dave

OzGrid Business Applications