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 Name: Controls Name Data Type Control Type Required Select Names... Create userform builder

In the UserForm Builder you will specify

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
ufmOrder.Show
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()
    Me.Hide
End Sub

Private Sub btnOk_Click()
    If IsInputOk Then
        IsCancelled = False
        Me.Hide
    End If
End SubSub

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 HandleExit: Exit Sub HandleError: Resume HandleExit End Sub Private Function GetValue(ctl As MSForms.Control, strTypeName As String) As 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 HandleExit: GetValue = value Exit Function HandleError: 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
            ctl.SetFocus
            GoTo HandleMessage
        End If
    Next
    IsInputOk = True
HandleExit:
    Exit Function
HandleMessage:
    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
HandleExit:
    Exit Function
HandleError:
    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