UserForms - controls collection

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:

  1. 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:

  1. Line 14: All of the code statements have been moved to txtNumberChange sub procedure in code 2c. Line 14 now calls the txtNumberChange procedure and passes the argument 1


Code 2b: txtNumber2 Change Event 
'' [frmMultiply (Code)]

'' =============================================
Private Sub txtNumber2_Change()
    txtNumberChange (2)
End Sub

Code 2b by line number:

  1. Line 24: The changes are the same as txtNumber2_Change event (code 2a). All of the code statements have been moved to txtNumberChange sub procedure in code 2c. Line 24 now calls the txtNumberChange procedure and passes the argument 2


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:

  1. Line 32: Procedure declaration: a Sub procedure named txtNumberChange with one argument named Num of type Integer. The txtNumber1_Change event passes the value 1, and txtNumber2_Change event passes the value 2
  2. Line 36: [Num = 1] Controls("txtNumber" & Num) returns the controls names txtNumber1
  3. Line 39: [Num = 1] The is the same logic as line 36. Controls("txtNumber" & Num) returns the controls names txtNumber1
  4. Line 42: [Num = 1] The numeric value in Temp is assigned to the array Number(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:

  1. 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:

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

multiplier-controls-collection
Fig 1: frmMultiplyControls procedure - output to the Immediate Window