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:

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 VBA Code

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

• Code to open / show the userform
• User Defined Type
• Validating user input

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

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
• Passing data to the userform
• Validating the entered data

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 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
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