User Form multiplication example - Controls
Preliminary reading User Form multiplication example with TextBoxes containing repeated code for the Change event. This module shows how to simplify the TextBox code with the use of the Controls object.
1. Module level declaration
Code 1: Module declaration, and txtNumber1 Change Event
'' [frmMultiply (Code)] Option Explicit Dim Number(1 To 2) As Double
Code 1 by line number:
- Line 5: The Number1 and Number2 variables are replaced by an array named
Number
, with two elements of type double. This allows the elements to be accessed with a text string and concatenation (see code 2c, line 36 for an example)
2. TextBox Change Event
Code 2a: txtNumber1 Change Event
'' [frmMultiply (Code)] '' ============================================= Private Sub txtNumber1_Change() txtNumberChange (1) End Sub
Code 2a by line number:
- Line 14: All of the code statements have been moved to
txtNumberChange
sub procedure in code 2c. Line 14 now calls thetxtNumberChange
procedure and passes the argument1
Code 2b: txtNumber2 Change Event
'' [frmMultiply (Code)] '' ============================================= Private Sub txtNumber2_Change() txtNumberChange (2) End Sub
Code 2b by line number:
- Line 24: The changes are the same as
txtNumber2_Change
event (code 2a). All of the code statements have been moved totxtNumberChange
sub procedure in code 2c. Line 24 now calls thetxtNumberChange
procedure and passes the argument2
Code 2c: txtNumberChange procedure
'' [frmMultiply (Code)] Private Sub txtNumberChange(Num As Integer) Dim temp As String temp = Controls("txtNumber" & Num).Text If Not IsNumeric(temp) And temp <> "" And temp <> "-" Then MsgBox "Enter Numbers Only", vbCritical, "Error" Controls("txtNumber" & Num) = "" txtResult.Text = "" Else number(Num) = Val(temp) calculator End If End Sub
Code 2c by line number:
- Line 32: Procedure declaration: a Sub procedure named
txtNumberChange
with one argument namedNum
of type Integer. The txtNumber1_Change event passes the value 1, and txtNumber2_Change event passes the value 2 - Line 36: [Num = 1] Controls("txtNumber" & Num) returns the controls names
txtNumber1
- Line 39: [Num = 1] The is the same logic as line 36. Controls("txtNumber" & Num) returns the controls names
txtNumber1
- Line 42: [Num = 1] The numeric value in
Temp
is assigned to the arrayNumber(1)
element
See section 5 for more information about the Controls object.
3. Multiplication engine
Code 3: Sub procedure Calculator
'' [frmMultiply (Code)] Private Sub calculator() txtResult.Text = number(1) * number(2) If Val(txtResult.Text) <> 0 Then cmdOutput.Enabled = True End Sub
Code 3 by line number:
- Line 54: The values in elements 1 and 2 of the
number
array are multiplied, then assigned to the txtResult TextBox object
4. Ordinary Code Module
Code 4: Sub procedure Multiply
'' [Multiplier (Code)] Sub Multiply() '' Allow the User to move the ActiveCell whilst the form is open FrmMultiply.Show vbModeless End Sub
Code 4 by line number:
- Line 64: This code is located in a normal module (Multiplier). The Show method displays the
FrmMultiply
UserForm, and the vbModeless property allows the user to switch to Excel and move the ActiveCell while the UserForm is still open. Note: is most cases a user form is Modal, meaning that it must be Unloaded or Hidden to allow the user to continue their task.
5. The Controls Collection
This section demonstrates the Controls collection. Members can be returned by Controls(index), where index is a zero base integer, or by Controls("name"), where name is the control name such as txtNumber1
.
Code 5: Sub procedure frmMultiplyControls
Sub frmMultiplyControls() Dim Ctrl As Control Dim CtrlCount As Integer Dim LblCount As Integer Dim i As Integer, j As Integer Dim LblArr() As Integer With FrmMultiply CtrlCount = .Controls.Count Debug.Print vbNewLine & "=========================" Debug.Print "Print time: " & Time Debug.Print vbNewLine & "No of controls = " & CtrlCount & vbNewLine '' List all controls (Immediate Window) Debug.Print "Names of controls =======" For Each Ctrl In .Controls i = i + 1 Debug.Print i & ". " & Ctrl.Name Next '' List TextBox controls (Immediate Window) i = 0 Debug.Print vbNewLine & "Names of TextBox controls" For Each Ctrl In .Controls If TypeName(Ctrl) = "TextBox" Then i = i + 1 Debug.Print i & ". " & Ctrl.Name End If Next Ctrl '' Count Label controls For Each Ctrl In .Controls If TypeName(Ctrl) = "Label" Then j = j + 1 End If Next Ctrl LblCount = j '' Assign Label Control(index) values to array '' Selected Label properties i = 0 j = 0 ReDim LblArr(1 To LblCount) For Each Ctrl In .Controls i = i + 1 If TypeName(Ctrl) = "Label" Then j = j + 1 LblArr(j) = i - 1 End If Next Ctrl '' List selected Control properties (Immediate Window) Debug.Print vbNewLine & "Label controls - selected properties" For i = 1 To LblCount Debug.Print "Control Index: " & LblArr(i) Debug.Print vbTab & "Name: " & .Controls(LblArr(i)).Name Debug.Print vbTab & "Caption: " & .Controls(LblArr(i)).Caption Debug.Print vbTab & "Left: " & .Controls(LblArr(i)).Left Debug.Print vbTab & "Top: " & .Controls(LblArr(i)).Top & vbNewLine Next i End With End Sub
The output from the code 5 Debug.Print statements is shown in figure 1

- The Excel file for this module: xlf-multiplier-v2.xlsm [44 KB]
- This example was developed in Excel 2013 :: VBA 7.1
- Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Time (AET)]