Creating a userform

Userforms are custom dialogs used to collect input data from the user at the start of a macro. The UserForm Builder included in Code VBA automatically makes UserForms. The generated user forms have with input validation code added to ensure the all required input is of the correct type. This will make the macro more reliable. Having the controls and validation code generated saves a lot of time you would have to spend to create the userform manually. The UserForm can be changed both to make the design visually more attractive by moving and resizing the controls. In addition you can extend the userform vba code to fulfill extra requirements.

Using the UserForm Builder

The UserForm Builder is started from the Code VBA menu:

userform builder

In the UserForm Builder you will specify

  • UserForm Name: give a name to the form which is short for the data that will be entered. In the example the name was 'Order'
  • The Controls grid is the area in which you specify which controls you want to have included in the UserForm you are currently creating. Each control is specified in a seperate line with properties:
    • Name: will be used as the label for the input control.
    • Data Type: if the user enters a value of the wrong type in the control, pressing the OK button results in a message to the user that a value for that field is incorrect and sets the cursor back to that field for the user to correct it.
    • Control Type: lets you specify what control type is preferred. A boolean (yes/no) can be represented in a user friendly way using a check
    • Required: if the user does not enter a value in the control, pressing the OK button results in a message to the user that a value is missing for that field and sets the cursor in that field.
  • Select Names...: opens a range selection inputbox to enable you to select one or more names from your Excel sheet. This is useful if your dialog is created to fill an existing Excel form or table.
  • Create: creates the UserForm - image below - and inserts the code to start the userform.
userform builder

UserForm VBA Code

In addition to the UserForm object, the builder also adds three pieces of code:

Code to open / show the userform

Assuming the cursor was inside the procedure Sub Demo, after pressing OK on the UserForm Builder the procedure would look like below.

Sub Demo()
    Dim udtOrder As Order
    With udtOrder
        .Client = ""
        .EntryDate = Date
        .Product = ""
        .Attention = True
    End With
    ufmOrder.FillList "cboProduct", Array("v1", "v2", "v3")
    ufmOrder.SetValues udtOrder
    If Not ufmOrder.IsCancelled Then
        ufmOrder.GetValues udtOrder
        'continue process after OK here
        With udtOrder
        End With
    End If
    Unload ufmOrder
End Sub

To get a clean interface a User Defined Type is used which comprises the data controls with their type definitions on the user form. You can alter the code between the first With and End With to change the initial or default values. Passing the values to the form is done in the method SetValues

The FillList procedure is used to pass an array of values to the Product list box. The values in the array ("v1",...) are just an example which you will have to adapt.

The Show method is the standard way to open the UserForm.

The user may press OK or Cancel the dialog. This is determined by testing the .IsCancelled property.

If the user pressed OK the macro will continue using the data the user provided in the userform. For this you will add your own process code between the second With and End With.

User Defined Type for a clean interface

The generated User Defined Type provides a clean interface between your macro and the UserForm. If the procedure you are calling the userform from is in a standard module, the User Defined Type will be placed in there. If not, it will be placed in a module called 'modTypes'. In the example the type declaration looks like this:

Public Type Order
    Client As String
    EntryDate As Date
    Product As String
    Attention As Boolean
End Type
Note - User Defined Type Builder
Similar to the builder explained here Code VBA also includes a User Defined Type Builder:

UserForm validation and other code

Inside the generated UserForm there is yet quite a lot of code which is used for:

Handling OK and Cancel

The code below shows the handling of Ok and Cancel. The Close button is also adequately handled as Cancel without needing extra code. The IsCancelled public variable is used to communicate to the calling macro if the user pressed Ok or Cancel.

Public IsCancelled As Boolean

Private Sub UserForm_Initialize()
    IsCancelled = True
End Sub

Private Sub btnCancel_Click()
End Sub

Private Sub btnOk_Click()
    If IsInputOk Then
        IsCancelled = False
    End If
End Sub

Passing data to the userform

The data is passed to and obtained from the userform by SetValues and GetValues respectively. Both use the User Defined Type variable.

Public Sub SetValues(udtOrder As Order)
    With udtOrder
        SetValue Me.txtClient, .Client
        SetValue Me.txtEntryDate, .EntryDate
        SetValue Me.cboProduct, .Product
        SetValue Me.cbxAttention, .Attention
    End With
End Sub

Public Sub GetValues(ByRef udtOrder As Order)
    With udtOrder
        .Client = GetValue(Me.txtClient, TypeName(.Client))
        .EntryDate = GetValue(Me.txtEntryDate, TypeName(.EntryDate))
        .Product = GetValue(Me.cboProduct, TypeName(.Product))
        .Attention = GetValue(Me.cbxAttention, TypeName(.Attention))
    End With
End Sub

SetValues and GetValues are implemented using below procedures:

Private Sub SetValue(ctl As MSForms.Control, value As Variant)
On Error GoTo HandleError
    ctl.value = value
    Exit Sub
    Resume HandleExit
End Sub

Private Function GetValue(ctl As MSForms.Control, strTypeName As StringAs Variant
On Error GoTo HandleError
    Dim value As Variant
    value = ctl.value
    If IsNull(value) And strTypeName <> "Variant" Then
        Select Case strTypeName
        Case "String"
            value = ""
        Case Else
            value = 0
        End Select
    End If
    GetValue = value
    Exit Function
    Resume HandleExit
End Function

Validating userform input

When the user presses OK, the data on the UserForm is validated by the IsInputOk function. This checks for each input control IsInputControl if it has a value HasValue in case it is required IsRequired. Next it checks if the value is of the correct type IsCorrectType - as was specified in UserForm Builder. Failure to pass either test results in a message to the user and putting the focus on the control whose value failed ctl.SetFocus.

Private Function IsInputOk() As Boolean
Dim ctl As MSForms.Control
Dim strMessage As String
    IsInputOk = False
    For Each ctl In Me.Controls
        If IsInputControl(ctl) Then
            If IsRequired(ctl) Then
                If Not HasValue(ctl) Then
                    strMessage = ControlName(ctl) & " must have value"
                End If
            End If
            If Not IsCorrectType(ctl) Then
                strMessage = ControlName(ctl) & " is not correct"
            End If
        End If
        If Len(strMessage) > 0 Then
            GoTo HandleMessage
        End If
    IsInputOk = True
    Exit Function
    MsgBox strMessage
    GoTo HandleExit
End Function

Note that IsCorrectType uses the function ControlDataType which simply returns the type for the given control using Select Case statements inserted by the UserForm Builder. A similar but simpler approach is followed for IsRequired.

Private Function IsCorrectType(ctl As MSForms.Control) As Boolean
Dim strControlDataType As String, strMessage As String
Dim dummy As Variant
    strControlDataType = ControlDataType(ctl)
On Error GoTo HandleError
    Select Case strControlDataType
    Case "Boolean"
        dummy = CBool(GetValue(ctl, strControlDataType))
    Case "Byte"
        dummy = CByte(GetValue(ctl, strControlDataType))
    Case "Currency"
        dummy = CCur(GetValue(ctl, strControlDataType))
    Case "Date"
        dummy = CDate(GetValue(ctl, strControlDataType))
    Case "Double"
        dummy = CDbl(GetValue(ctl, strControlDataType))
    Case "Decimal"
        dummy = CDec(GetValue(ctl, strControlDataType))
    Case "Integer"
        dummy = CInt(GetValue(ctl, strControlDataType))
    Case "Long"
        dummy = CLng(GetValue(ctl, strControlDataType))
    Case "Single"
        dummy = CSng(GetValue(ctl, strControlDataType))
    Case "String"
        dummy = CStr(GetValue(ctl, strControlDataType))
    Case "Variant"
        dummy = CVar(GetValue(ctl, strControlDataType))
    End Select
    IsCorrectType = True
    Exit Function
    IsCorrectType = False
    Resume HandleExit
End Function

Private Function ControlDataType(ctl As MSForms.Control) As String
    Select Case ctl.Name
    Case "txtClient": ControlDataType = "String"
    Case "txtEntryDate": ControlDataType = "Date"
    Case "cboProduct": ControlDataType = "String"
    Case "cbxAttention": ControlDataType = "Boolean"
    End Select
End Function
Private Function IsRequired(ctl As MSForms.Control) As Boolean
    Select Case ctl.Name
    Case "txtClient", "txtEntryDate", "cboProduct", "cbxAttention"
        IsRequired = True
    Case Else
        IsRequired = False
    End Select
End Function