# User Form multiplication example

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

1. Open the Visual Basic Editor (Alt+F11 from Excel)
2. Select View > Project Explorer from menu (if it isn’t already visible)
3. Select the project you want to add the UserForm to
4. Select Insert > UserForm from the menu
6. Click on this to see the list of forms in your project
7. You can add more than one form to a project
8. You can view a form by double-clicking its name.

### 1.2 Steps to creating a UserForm

Create a UserForm

1. Insert a new UserForm (section 1.1) and set its properties (section 2.1)
2. Draw controls on it
3. Set the properties for the controls (section 2)
4. Write the code to respond to the controls on the UserForm (section 3.1, and 3.3)
5. Write any other procedures for the program (section 3.2)
6. Add a normal module to the project, and write a procedure to run the UserForm based program (section 4)
7. 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

1. Three x Label objects (Number One, Number Two, and Result)
2. Three x TextBox objects
3. 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

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

1. Line 1: Module level declaration: Option  Explicit - all variables, constants and arrays must have an explicit declaration statement (Dim or Const)
2. Line 3: Declare a variable named Number1 as type Double. Module scope. Default value: 0
3. Line 4: Declare a variable named Number2 as type Double. Module scope. Default value: 0
4. • 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.
5. Line 6: A comment line
6. Line 7: The procedure declaration line. The Change event of the txtNumber1 TextBox
7. Line 8: Declare a variable named Temp as type String. Procedure scope. Default value: ""
8. Line 9: Assign the text string from txtNumber1 to the Temp variable
9. Line 10: Apply a logical test to the single string in the Temp variable
10. • 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)
11. • 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
12. Lines 11, 12 and 13: If line 10 is TRUE then display message box, and Clear text property in txtNumber1 and txtResult
13. 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.
14. Line 16: Call the Multiplier procedure to execute the code
15. Line 17: End of the If...Then...Else code block
16. 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.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

1. 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
2. • An alternative technique is a For Each...Next loop. The necessary (equivalent) code is:
3. 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


4. 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()
End Sub


Code 5

1. Line 70: The OK button Click event
2. Line 71: Unload the form from computer memory
3. • 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:
4.         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