Pass Data To\From UserForm

Home Up Odds & Ends Photo Gallery Search Contact Me

 

 

 The information in this website is provided without risk or obligation and free of charge.  However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal.  Thank you.
 

Often users (and not too long ago myself) ask, "How do I pass data from a macro to a UserFom or vice versa.?"  This Microsoft Word Help & Tips page has been reworked extensively after I received a friendly rebuke from one VBA expert and fellow MVP plus some valuable schooling from him and and several others.

:old:Bonus Tip:  A primer for a basic UserForm and other helpful tips on UserForms can be found here:  UserForms.  When the page opens, click the "UserForm" tab.

The examples given here aren't a practical solution to a specific task, but provided to illustrate a few of the various methods available.  The methods illustrate both the calling macro code and UserForm code.  A link for downloading a template containing the macros and UserForms is located at the end of this tips page.

When you have downloaded and installed the template you can view the UserForms and calling macro code in the VBA Editor.  To access the editor in Word, press ALT+F11.  To ensure you have a similar view (including the Project Explorer and Properties Window) to match the following screen shots, after the editor opens press F4 and CTRL+r.

The calling macros use a familar InputBox to allow you to enter a string of text to pass to the UserForm.  The calling macro PassData1 uses a input box with "default" text.  Again in a practical situation, you wouldn't even need to pass data in this method.  It is provide here just to illustrate how it could be done.

In the illustration above, the user is passing a value from the calling macro to set the Caption property of the UserForm.  Direct manipulation of the form is one method of passing data.  As you can see below, the called UserForm displays the text received from the calling macro and the interface to pass data back to the calling macro.

The calling macro uses a MsgBox to display the text passed by the UserForm.

My Sensei calls the first method the "Look Ma, no variables method."  As you will see in the calling macro and UserForm code below, there is no "Public" declared variable used to pass data to and from the calling macro and UserForm.
  Sub PassData1()
Dim myFrm As oFrm1
Set myFrm = New oFrm1
myFrm.Frame1.Caption = InputBox("Enter a custom Frame1 Caption: ", _
              "Custom Caption", "Enter a standard brassiere size e.g., 34D")
myFrm.Show
If myFrm.TextBox1.Text <> "Canceled" Then
    MsgBox "You entered size " & UCase(myFrm.TextBox1.Text) & ". This" _
                     & " is a valid size.", , "Data Returned"
End If
Unload myFrm
Set myFrm = Nothing
End Sub
 

Here is the UserForm code:

Option Explicit
Private CancelEvents As Boolean
Private Sub CommandButton1_Click()
If Not CancelEvents Then
    If SizeValidator(Me.TextBox1.Text) Then
        Me.Hide
    Else
        With Me
            .Frame1.Caption = Me.TextBox1.Text & " is invalid." _
                           & " Please enter a valid size."
            With .TextBox1
                .SetFocus
                .SelStart = 0
                .SelLength = Len(.Text)
            End With
        End With
    End If
End If
End Sub

Private Sub UserForm_Initialize()
Me.Caption = "Hi " & Application.UserName & "!"
CancelEvents = False
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then
    CancelEvents = True
    Me.TextBox1.Text = "Canceled"
    End If
    End Sub
Note:  The UserForm CommandButton1 Click event includes a Function to validate the entry in the text box.  The code for the validation function is included in the downloadable template linked at the end of this Tips page.

 

The remaining four methods are provided in the illustrations below.  These include using:  1) A document variable (DocVariable) where the calling macro writes to the variable, the UserForms reads the variable, the UserForm over-writes the variable, and finally the calling macro reads the new information in the variable,  2) A Class object in the project.  Data is passed using an object variable,  3) A "Public" variable declared at the module level (Note - a "Public" variable declared at the module level is available to "all" other procedures (and UserForms) in all modules, and probably the least practical, 4) A custom UserForm property using Get and Let procedures.
All four methods use a similiar input box to pass data to the UserForm:

A UserForm displays the text received from the calling macro and the interface to pass data back to the calling macro.

Again, the calling macro uses a similiar MsgBox to display the text passed by the UserForm.

The above illustrations provide a look at the facade of calling macro and UserForm interface.  You can download a file containing the complete code to view and observe these methods here:  PassData.doc

Need help applying macros?  See fellow MVP Graham Mayor's  Guide for Installing Macros

Looking for something else?

Google