Mandatory field Checker in VBA

Home > Programming > Mandatory field Checker in VBA

June 17th, 2008 Leave a comment Go to comments

In VBA programming, it’s time consuming when it come to checking and validating the data entry, specially those fields that are mandatory. You might have to write hundreds lines of code (depends on how big your form is) to handle such incident. You might feel like, if there’s should be something “Mandatory Checker” to handle this part of development. The new .NET framework included this feature in Application Development environment but unfortunately they did not include it in VBA.

I though I would spend sometime to write this class (MandatoryChecker) and reuse it in the future development when needed. So if you are a VBA programmer, I presume this will help you quite well and save you time validating the mandatory field entry on the form.

This class was written a few years ago and I believe it’s still beneficial for those who’s current developing Word Macro Form.

Source Code

Class Module: MandatoryChecker

'Class:         MandatoryChecker
'Description:   Check the mandatory fields that required to be filled or selected
'Author:        Manet Yim (manet.yim at gmail dot com)
'Class Verion:  1.3
'
'   How to use it:
'   - Create MandatoryChecker object
'   - List All Mandatory Objects (eventually add to list)
'   - Call Check function to perform checking, and provide the "Form Name" where the objects are
'
'   Example:
'        Private MC As New MandatoryChecker
'        Dim i as Integer
'        MC.AddMandatoryObject ControlObject.Name
'          .
'          .
'        i = CheckMandatoryWithListed(FormName)
'
'When i = 0 : Sccess, i &gt; 0 : Not Success, there's mandator(y/ies) field(s) left blank
Option Explicit
 
' A Collection of Exceptional Object which will not be checked
Private ExceptionObjects As New Collection
' A Collection of Mandatory Object which will be checked
Private MandatoryObjects As New Collection
' A Collection of Group Name of Option Buttons
Private GroupNames As New Collection
' A color for mandatory field background
Private Const MandatoryBgColor As String = &amp;HC0FFFF
 
Private Sub Class_Initialize()
' Init Class config

End Sub
 
Public Sub AddMandatoryObject(Obj As Variant)
' Add Mandatory Object to List
    MandatoryObjects.Add Obj
End Sub
 
Public Sub AddExceptionObject(Obj As Variant)
' Add Exceptional Object to List
    ExceptionObjects.Add Obj
End Sub
 
Public Sub RemoveExceptionObject(Obj As Variant)
' Remove Exceptional Object from List
    Dim X As Integer
    Dim MAX As Integer
    MAX = ExceptionObjects.Count
    For X = 1 To MAX
        If Obj = ExceptionObjects.Item(X) Then
            ExceptionObjects.Remove X
        End If
    Next
End Sub
 
Public Sub RemoveMandatoryObject(Obj As Variant)
' Remove Mandatory Object from List
    Dim X As Integer
    Dim MAX As Integer
    MAX = MandatoryObjects.Count
    For X = 1 To MAX
        If Obj = MandatoryObjects.Item(X) Then
            MandatoryObjects.Remove X
        End If
    Next
End Sub
 
Public Sub ClearExceptionObjects()
' Clear all Exception Objects
    Do While Not (ExceptionObjects.Count = 0)
        ExceptionObjects.Remove 1
    Loop
End Sub
 
Public Sub ClearMandatoryObjects()
' Clear all Mandatory Objects
    Do While Not (MandatoryObjects.Count = 0)
        MandatoryObjects.Remove 1
    Loop
End Sub
 
Public Function ExceptionListSize() As Integer
' Return the size of Exception Objects List size
    ExceptionListSize = ExceptionObjects.Count
End Function
 
Public Function MandatoryListSize() As Integer
' Return the size of Mandatory Objects List size
    MandatoryListSize = MandatoryObjects.Count
End Function
 
Public Function CheckMandatoryAllFields(FormName As UserForm) As Integer
 
' The availabled Input Types that will be checked:
'    TextBox, ListBox, ComboBox, CheckBox

' Return Value
'   0(Zero)     :No Madatory field
'   #(1 or more):There is/are mandatory field(s)

    Dim c_FormObject As Control
    Dim ErrorCount As Integer
 
    ErrorCount = 0
 
    For Each c_FormObject In FormName.Controls
        'Control Object is TextBox
        If TypeOf c_FormObject Is TextBox Then
            If c_FormObject.Text = "" Then
                ErrorCount = ErrorCount + 1
            End If
        'Control Object is ComboBox
        ElseIf TypeOf c_FormObject Is ComboBox Then
            If c_FormObject.Text = "" Then
                ErrorCount = ErrorCount + 1
            End If
        'Control Object is CheckBox
        ElseIf TypeOf c_FormObject Is CheckBox Then
            If c_FormObject.Value = False Then
                ErrorCount = ErrorCount + 1
            End If
        'Control Object is ListBox
        ElseIf TypeOf c_FormObject Is ListBox Then
            If c_FormObject.Selected = False Then
                ErrorCount = ErrorCount + 1
            End If
        End If
    Next
 
    CheckMandatoryAllFields = ErrorCount
End Function
 
Public Function CheckMandatoryWithListed(CtrUserForm As UserForm) As Integer
 
' Check Mandatory field in the form that listed in collection.
' The availabled Input Types that will be checked:
'    TextBox, ListBox, ComboBox, CheckBox

' Return Value
'   0(Zero)     :No Madatory field
'   #(1 or more):There is/are mandatory field(s)

    Dim TmObject As String
    Dim TmObjType As String
    Dim TmControl As Control
    Dim Success As Boolean
    Dim ErrorCount As Integer
 
    ErrorCount = 0
 
    For Each TmControl In CtrUserForm.Controls
        TmObjType = TypeName(TmControl)
        TmObject = TmControl.Name
        If InMadList(TmObject) Then                    'Check in Mandatory List
            Select Case TmObjType
                Case "TextBox":
                'Control Object is TextBox
                    If TmControl.Text = "" Then
                        ErrorCount = ErrorCount + 1
                    End If
                Case "ComboBox":
                'Control Object is ComboBox
                    If TmControl.ListIndex = 0 Then
                        ErrorCount = ErrorCount + 1
                    End If
                Case "ListBox":
                'Control Object is ListBox
                    If TmControl.Selected = False Then
                        ErrorCount = ErrorCount + 1
                    End If
                Case "CheckBox":
                'Control Object is CheckBox
                    If TmControl.Value = False Then
                        ErrorCount = ErrorCount + 1
                    End If
            End Select
        End If
    Next
 
    ' Assign the return value (the number of error occured)
    CheckMandatoryWithListed = ErrorCount
End Function
 
Public Function CheckMandatoryWithException(CtrUserForm As UserForm) As Integer
' Check Mandatory fields in the form that is not listed in Collection
' The availabled Input Types that will be checked:
'    TextBox, ListBox, ComboBox, CheckBox

' Return Value
'   0(Zero)     :No Madatory field
'   #(1 or more):There is/are mandatory field(s)

    Dim TmObject As String
    Dim TmObjType As String
    Dim TmControl As Control
    Dim Success As Boolean
    Dim ErrorCount As Integer
 
    ErrorCount = 0
 
    For Each TmControl In CtrUserForm.Controls
        TmObjType = TypeName(TmControl)
        TmObject = TmControl.Name
        If Not InExpList(TmObject) Then             'Check in Exception List
            Select Case TmObjType
                Case "TextBox":
                'Control Object is TextBox
                    If TmControl.Text = "" Then
                        ErrorCount = ErrorCount + 1
                    End If
                Case "ComboBox":
                'Control Object is ComboBox
                    If TmControl.ListIndex = 0 Then
                        ErrorCount = ErrorCount + 1
                    End If
                Case "ListBox":
                'Control Object is ListBox
                    If TmControl.Selected = False Then
                        ErrorCount = ErrorCount + 1
                    End If
                Case "CheckBox":
                'Control Object is CheckBox
                    If TmControl.Value = False Then
                        ErrorCount = ErrorCount + 1
                    End If
            End Select
        End If
    Next
 
    ' Assign the return value (the number of error occured)
    CheckMandatoryWithException = ErrorCount
End Function
 
Private Function InMadList(Obj As Variant) As Boolean
'Check in Mandatory List
    Dim X As Integer
    Dim MAX As Integer
    MAX = MandatoryObjects.Count
    For X = 1 To MAX
        If Obj = MandatoryObjects.Item(X) Then
            InMadList = True
            Exit Function
        Else
            InMadList = False
        End If
    Next
End Function
 
Private Function InExpList(Obj As Variant) As Boolean
'Check in Exception List
    Dim X As Integer
    Dim MAX As Integer
    MAX = ExceptionObjects.Count
    For X = 1 To MAX
        If Obj = ExceptionObjects.Item(X) Then
            InExpList = True
            Exit Function
        Else
            InExpList = False
        End If
    Next
End Function