User Form multiplication example
User Forms - Custom dialog boxes
1. What is a UserForm?
UserForms (forms):
- are custom dialog boxes that can be added to a VBA project
- allow the user to create dialog boxes, like the ones that are built into Excel and other Windows based programs
- allow greater control over the way the program is run. The corollary is that the programming is more challenging, as there are a more interlinks between form components
- are a preferred interface design to input boxes because User Form controls are not sequential in operation (link: a sequence of input boxes)
- are also preferable, as they can allow the user to have easier decision choices from a custom interface
1.1 Adding a UserForm to the Project
To add a UserForm to a VBA project, you must do the following
- Open the Visual Basic Editor (Alt+F11 from Excel)
- Select View > Project Explorer from menu (if it isn’t already visible)
- Select the project you want to add the UserForm to
- Select Insert > UserForm from the menu
- Doing this will add a Forms folder to your project
- Click on this to see the list of forms in your project
- You can add more than one form to a project
- You can view a form by double-clicking its name.
1.2 Steps to creating a UserForm
Create a UserForm
- Insert a new UserForm (section 1.1) and set its properties (section 2.1)
- Draw controls on it
- Set the properties for the controls (section 2)
- Write the code to respond to the controls on the UserForm (section 3.1, and 3.3)
- Write any other procedures for the program (section 3.2)
- Add a normal module to the project, and write a procedure to run the UserForm based program (section 4)
- Add a hotkey or menu item to run the procedure (section 5)
2. frmMultiply Form Module
Our aim is to develop a “Multiplication Program” UserForm (figure 1), by following the steps listed in section 1.2. Remember: Objects - Properties, Methods, and Events.

The form contains the following objects
- Three x Label objects (Number One, Number Two, and Result)
- Three x TextBox objects
- Two x CommandButton objects (OK, and OUTPUT)
The multiplication result is triggered by the user typing in one of the two input boxes. This is described as the object Change event Thus the form procedures need to handle cases where the TextBox text is not a number - see code 1 line 10.
2.1 frmMultiply UserForm
Custom properties:
- (Name):
frmMultiply
- Caption:
xlf :: Multiplication Program
- Height:
115
- Width:
240
- StartUpPosition:
1 - CentreOwner
2.2 Label controls
2.3 TextBox controls
3. frmMultiply Code Module
It is good practice to name the form control objects BEFORE writing the code. Renaming objects later will not automatically update the associated code. All UserForm module procedures are of the type Private. This prevents procedures from appearing on the Macros list.
3.1 TextBox Change Event
This module uses the TextBox_Change event, an alternate technique is the TextBox_KeyPress event (see multiplication program - KeyPress version)
Code 1: Module declaration, and txtNumber1 Change Event
Option Explicit Dim Number1 As Double Dim Number2 As Double '' ============================================= Private Sub txtNumber1_Change() Dim Temp As String Temp = txtNumber1.Text If Not IsNumeric(Temp) And Temp <> "" And Temp <> "-" Then MsgBox "Enter Numbers Only", vbCritical, "Error" txtNumber1.Text = "" txtResult.Text = "" Else Number1 = Val(Temp) Multiplier End If End Sub
Code 1 by line number:
- Line 1: Module level declaration:
Option
Explicit - all variables, constants and arrays must have an explicit declaration statement (Dim or Const) - Line 3: Declare a variable named Number1 as type
Double
. Module scope. Default value: 0 - Line 4: Declare a variable named Number2 as type
Double
. Module scope. Default value: 0 - • number1 and number2 are declared at the Module level. This means their values are available to all procedures within the module. Once values are assigned , the variables retain the values during the current session until the form Unload event.
- Line 6: A comment line
- Line 7: The procedure declaration line. The Change event of the txtNumber1 TextBox
- Line 8: Declare a variable named Temp as type
String
. Procedure scope. Default value: "" - Line 9: Assign the text string from txtNumber1 to the Temp variable
- Line 10: Apply a logical test to the single string in the Temp variable
- • The logical test returns TRUE if the value of Temp is NOT a number, and NOT a minus sign, and NOT a "" (caused by the user entering a Backspace or Delete key action in txtNumber1)
- • The statement
If Not IsNumeric(Temp) And Temp <> "" And Temp <> "-" Then
returns TRUE if ALL conditional tests are TRUE, else it returns FALSE. See TextBox prefilter for further details - Lines 11, 12 and 13: If line 10 is TRUE then display message box, and Clear text property in txtNumber1 and txtResult
- Lines 14 and 15: Else (if line 10 is FALSE), assign the numeric component of Temp to Number1 of type Double. The Val function returns the leading numbers from a text string as far as the first non numeric value.
- Line 16: Call the Multiplier procedure to execute the code
- Line 17: End of the If...Then...Else code block
- Line 18: End statement in the txtNumber1 sub procedure
Code 2: txtNumber2 Change Event
Private Sub txtNumber2_Change() Dim Temp As String Temp = txtNumber2.Text If Not IsNumeric(Temp) And Temp <> "" And Temp <> "-" Then MsgBox "Enter Numbers Only", vbCritical, "Error" txtNumber2.Text = "" txtResult.Text = "" Else Number2 = Val(Temp) Call Multiplier End If End Sub
Code 2, lines 21 to 32, is the same logic as code 1 - lines 7 to 18. The only difference is that the Multiplier procedure in line 30 is explicitly called with the Call statement. Using the Call statement can make it easier to identify VBAProject methods. Code 1 and 2 can be simplified by using the Controls collection (see xlf-multiplier-program-v2 User Form multiplication example - Controls)
3.2 Multiplication engine
Code 3: Sub procedure Calculator
Private Sub multiplier() txtResult.Text = Number1 * Number2 If Val(txtResult.Text) <> 0 Then cmdOutput.Enabled = True End Sub
3.3 CommandButton Click Event
3.3.1 The OUTPUT button
In this example, the OUTPUT button click event writes to the ActiveCell.
Code 4: Sub procedure cmdOutput Click Event
Private Sub cmdOutput_Click() Dim Button As Integer Dim i As Integer, j As Integer Dim NotEmpty As Boolean For i = 0 To 3 For j = 0 To 1 If Not IsEmpty(ActiveCell.Offset(i, j)) Then NotEmpty = True End If Next j Next i If NotEmpty Then Button = MsgBox("OK to over write existing data?", vbYesNo + vbInformation, "xlf :: Multiplication Output") If Button = vbNo Then Exit Sub End If With ActiveCell .Offset(0, 0).Value = "xlf :: Multiplier Program" .Offset(1, 0).Value = lblNumber1.Caption .Offset(1, 0).ColumnWidth = 14 .Offset(1, 1).Value = txtNumber1.Text .Offset(2, 0).Value = lblNumber2.Caption .Offset(2, 1).Value = txtNumber2.Text .Offset(3, 0).Value = lblResult.Caption .Offset(3, 1).Value = txtResult.Text End With End Sub
Code 4
- Lines 45 to 51: a double For...Next loop is used to identify non empty cells in the target output area (anchored on the ActiveCell object
- • An alternative technique is a For Each...Next loop. The necessary (equivalent) code is:
Private Sub cmdOutput_Click() Dim Button As Integer Dim NotEmpty As Boolean Dim Item As Range For Each Item In ActiveCell.Resize(4, 2) If Not IsEmpty(Item) Then NotEmpty = True End If Next Item
- Each Item is processed on a row by row basis
3.3.1 The OK button
Code 5: Sub procedure cmdOK Click Event
Private Sub cmdOK_Click() Unload frmMultiply End Sub
Code 5
- Line 70: The OK button Click event
- Line 71: Unload the form from computer memory
- • You can use Me to refer to the parent object of location of the code (the code window in the frmMultiply module). In this case it is the UserForm. An equivalent code statement is:
Unload Me
4. Ordinary Code Module
Code 6: Sub procedure cmdOK Click Event
Sub Multiply() '' Allow the User to move the ActiveCell whilst the form is open FrmMultiply.Show vbModeless End Sub
- Associated material: More information about the UserForm Controls collection: link xlf-multiplier-program-v2 User Form multiplication example - Controls
- The Excel file for this module: xlf-multiplier.xlsm [44 KB]
- Development platform: Excel 2013 :: VBA 7.1
- Published: 30 September 2015
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Time (AET)]