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:
|Alt-CUUB | Menu: Code VBA » Dialog » UserForm »|
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:
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
End With to change the initial or default values.
Passing the values to the form is done in the method
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.
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
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
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:
Inside the generated UserForm there is yet quite a lot of code which is used for:
The code below shows the handling of Ok and Cancel. The Close button is also adequately handled as Cancel without needing extra code.
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
The data is passed to and obtained from the userform by
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
Private Sub SetValue(ctl As MSForms.Control, value As Variant)
On Error GoTo HandleError
ctl.value = value
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
value = ""
value = 0
GetValue = value
GetValues are implemented using below procedures:
When the user presses OK, the data on the UserForm is validated by the
This checks for each input control
IsInputControl if it has a value
HasValue in case it is required
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
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
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
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