In this article, I’d like to describe how to validate empty value in controls on UserForm of Excel VBA. It’s list of controls that you can select and enter on form.
- CheckBox
- OptionButton
- ComboBox
- ListBox
- TextBox
It’s assumed that OptionButtons and CheckBoxes are placed in Frame and they aren’t placed within one Frame together.
It is needed to designe as public function that validates empty value.
The function that validates empty value of controls on forms is called from multiple CommandButtons. Therefore, it’s reasonable to design as public function. It’s needed to add module for implementing the function. Data type of return value is Variant as following code because it returns name list of empty controls as string. It may be Boolean if you don’t have to present message box.
Option Explicit
Function CheckControls(myForm As MSForms.UserForm) As Variant
Dim Ctrl As MSForms.Control
Dim CheckCnt As Long
Dim myCnt As Long
Dim CheckStr As String
CheckControls = False
CheckCnt = 0
myCnt = 0
CheckStr = ""
For Each Ctrl In myForm.Controls
Select Case TypeName(Ctrl)
Case "ComboBox"
If Ctrl.ListIndex <> -1 Then
myCnt = myCnt + 1
Else
CheckStr = CheckStr & Ctrl.Name & vbCrLf
End If
Case "Frame"
If CheckFrame(Ctrl) Then
myCnt = myCnt + 1
Else
CheckStr = CheckStr & Ctrl.Name & vbCrLf
End If
Case "ListBox"
If Ctrl.ListIndex <> -1 Then
myCnt = myCnt + 1
Else
CheckStr = CheckStr & Ctrl.Name & vbCrLf
End If
Case "TextBox"
If Ctrl.Text <> "" Then
myCnt = myCnt + 1
Else
CheckStr = CheckStr & Ctrl.Name & vbCrLf
End If
Case Else
CheckCnt = CheckCnt - 1
End Select
CheckCnt = CheckCnt + 1
Next Ctrl
If CheckCnt = myCnt Then
CheckControls = True
Else
CheckControls = CheckStr
End If
End Function
Function CheckFrame(myFrame As MSForms.Frame) As Boolean
Dim FrmCnt As Long
Dim ChkCnt As Long
Dim OptCnt As Long
Dim tmpCtrl As Control
CheckFrame = False
FrmCnt = 0
ChkCnt = 0
OptCnt = 0
For Each tmpCtrl In myFrame.Controls
Select Case TypeName(tmpCtrl)
Case "CheckBox"
If tmpCtrl.Value Then
ChkCnt = ChkCnt + 1
End If
Case "OptionButton"
If tmpCtrl.Value Then
OptCnt = OptCnt + 1
End If
End Select
Next tmpCtrl
FrmCnt = FrmCnt + 1
If FrmCnt = OptCnt Or FrmCnt <= ChkCnt Then
CheckFrame = True
End If
End Function
The code that calls the function
You would write the code on click event as below. You could write the procedure between "If ... Then" and "Else" statements that is activated when it has passed verification.
Option Explicit
Private Sub CommandButton1_Click()
If TypeName(CheckControls(Me)) = "Boolean" Then
Else
MsgBox Prompt:=CheckControls(Me) & "Missing value above.", Title:="Empty values!"
Exit Sub
End If
End Sub
CheckBox could take NULL
To tell the truth, CheckBox has interesting property. Although OptionButton tekes only TRUE or FALSE, CheckBox takes three-valued logic with NULL. It's very difficult problem for database designers because NULL brings them unexpected results in query. Three-valued logic is the greatest weakness of relational model. TripleState property, its default value is FALSE, could select whether three-valued logic would be allowed or not.
It's assumed that one or more controls are checked in Frame. If the procedure validates naked CheckBox without Frame, what happens? Whatever the value of CheckBox is, it passes verification. You shouldn't use three-valued logic to select TURE or FALSE only.
Three-valued logic and set of choices
Furthermore, to allow multiple choices means that users would be allowed to not select any options more. Relation of 1:0 and 1:n are allowed, respectively. Fortunately, the situation could be avoided with careful design of choices. It's needed to be carefully treated of "Other case" that couldn't be treated as number. It's impossible to isolate and salvage data that is classified as "Others".
References:
Userform of Excel VBA as user interface
Three-valued logic (Wikipedia)