User Form multiplication example


User Forms - Custom dialog boxes


1. What is a UserForm?


UserForms (forms):


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
  5. Doing this will add a Forms folder to your project
  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.

xlf-multiplication-program
Fig 1: User Form: - multiplication program

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:


2.2 Label controls


Link to source file: 2.2.x php

2.3 TextBox controls


Link to source file: 2.3.x php

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.



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

  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()
    Unload frmMultiply
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