Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Pass Data To/From a Userform
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!

Click to donate British Pound Sterling                   Click to donate US dollars                   Click to donate EU euros

This Microsoft Word Tips & Microsoft Word Help page attempts to answer a frequently asked question from VBA enthusiast wanting to employ userforms: "How do I pass data from a calling VBA procedure to userform or vice versa.?""

If you are unfamiliar with userforms, see my:  Create & Employ a Userform

The examples I will provide will illustrate a few of the various methods available. These examples illustrate both the calling procedure and userform code. You can download the demonstration document which includes the procedures and userforms used to create this tips page here:  Userform Pass Data

Basic Example

The calling procedure for the this example is shown below:

VBA Script:
Sub PassData1()
Dim myFrm As UserForm1
Dim strCaption As String
  'Get a dynamic value to be passed to the userform from the user.
  Do
    'Define dynamic caption for userform frame control.
    strCaption = InputBox("Enter a custom Frame1 Caption: ", _
        "Custom Caption", "Enter your shoe size e.g., 10D")
    'Did the user cancel the inputbox?
    If StrPtr(strCaption) = 0 Then Exit Sub
  Loop Until strCaption <> ""
  'Create\load the userform in memory.
  Set myFrm = New UserForm1
  With myFrm
    'Employ a direct reference a form built-in property.
    .Frame1.Caption = strCaption
    'Display the form.
    .Show
    If .Tag <> "Canceled" Then
      'Employ a direct reference to a form built-in property.
      MsgBox "You entered size " & UCase(.TextBox1.Text) & ". This" _
           & " is a valid size.", , "Data Returned"
    End If
  End With
  Set myFrm = Nothing
End Sub

This example uses a simple InputBox function to prompt the user for dynamic information to pass to the userform.  This information is used to set caption text for a userform control.

userform_pass_data_1

A direct reference to the form's .Frame1 control, built-in .Caption property is a simple method of passing data. As you can see below, the called userform displays caption text provided by the user in the calling procedure.

userform_passed_data_2

The code for the userform is provided below:

VBA Script:
Private Sub cmdOK_Click()
  If fcnSizeValidator(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
lbl_Exit:
  Exit Sub
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  'Intercept/repurpose Unload if user clicks form "X" close button.
  If CloseMode = 0 Then
    Cancel = True
    Me.Tag = "Canceled"
    Me.Hide
  End If
lbl_Exit:
  Exit Sub
End Sub

After the user enters a shoe size and clicks the userform "OK" command button, the userform cmdOK_Click procedure evaluates a valid data entry by passing the user entered data as as argument to a separate function procedure in a standard project module. The function procedure is shown below:

VBA Script:
Function fcnSizeValidator(strSize As String) As Boolean
  strSize = UCase(strSize)
  Select Case True
    Case Is = Val(Left(strSize, 2)) <= 16 _
              And Val(Left(strSize, 2)) >= 2
      Select Case True
        Case Is = strSize Like "##[C-E]" Or _
          strSize Like "#.#[C-E]" Or _
          strSize Like "##.#[C-E]" Or _
          strSize Like "##[C-E][C-E]" Or _
          strSize Like "#.#[C-E][C-E]" Or _
          strSize Like "##.#[C-E][C-E]" Or _
          strSize Like "##[C-E][C-E][C-E]" Or _
          strSize Like "#.#[C-E][C-E][C-E]" Or _
          strSize Like "##.#[C-E][C-E][C-E]"
          fcnSizeValidator = True
        Case Else
          fcnSizeValidator = False
        End Select
    Case Else
      fcnSizeValidator = False
  End Select
lbl_Exit:
  Exit Function
End Function

When the user enters a valid size the cmdOK_Click procedure runs to termination and control is returned to the calling procedure.

In the calling procedure the data entered in the userform is returned using a simple VBA MsgBox function. Since the userform is still loaded in memory, its properties (i.e., .Textbox1.Text) can be evaluate directly in the calling procedure.

userform_pass_data_3

One of my early Senseis called this simple method the "Look Ma, no public variables method." There are no "Public" declared variables used to pass data to and from the calling procedure, the userform or other external processing procedures.

While simple, the method has a drawback.  The drawback is that the form's controls are not encapsulated and are exposed to external procedures.  For more on the importance of encapsulating userform code, see:  UserForm – Advanced Notes & Tips.

Encapsulated Methods

You can encapsulate and avoid exposing your userform controls to external procedures by using one of the following four methods:

  1. Define document variables where the calling procedure writes to a variable, the userform reads the variable, the userform writes to a variable, and finally the calling procedure reads the variable.
  2. Publically declared data variables.
  3. Userform custom properties with data variables declared publically in the userform class module.
  1. Userform custom properties with data variables declared privately in the userform class module.

A userform displays data received from the calling macro and the interface to collect data for passing data back to the calling procedure.

userform pass data 5

Again, the calling procedure uses a simple MsgBox function to display the data passed by the userform:

userform pass data 6

The code for the calling procedure and userform is provided below:

VBA Script (standard code module):
'Public data strings
Public p_strCaptionLabel As String
Public p_strAnswerReturned As String

Sub PassData2()
Dim myFrm As UserForm2
  'Method 1 - Store the text to pass in a document variable.
  ActiveDocument.Variables("txtPassedToForm").Value = fcnCaptionText(2)
  'Method 2 - Pass data to form using a public declared variable.
  p_strCaptionLabel = fcnCaptionText(1)
  'Create/load instance of form.
  Set myFrm = New UserForm2
  With myFrm
    'Method 3 - Custom userform property w\publically declared data variables.
    myFrm.LabelCaption = fcnCaptionText(3)
    'Method 4 - Custom userform property w\privately declared data variables.
    'Pass (or Let) fcnCaptionText value to private form variable.
    .CaptionLabel = fcnCaptionText(4)
    .Show
  End With
  If Not myFrm.UserCancel Then
    'Method 1.
    MsgBox "Answer 1: " _
    & ActiveDocument.Variables("txtPassedToCall").Value, , "Text Returned"
    'Method 2.
    MsgBox "Answer 2: " _
    & p_strAnswerReturned, , "Text Returned"
    'Method 3.
    MsgBox "Answer 3: " & myFrm.UserAnswer, , "Text Returned"
    'Method 4.
    MsgBox "Answer 4: " & myFrm.Answer, , "Text Returned"
  End If
lbl_Exit:
  Exit Sub
End Sub

Function fcnCaptionText(ByRef lngCount As Long) As String
  Select Case lngCount
    Case 1: fcnCaptionText = "What is your name?"
    Case 2: fcnCaptionText = "How old are you?"
    Case 3: fcnCaptionText = "Where do you live?"
    Case 4: fcnCaptionText = "Are you growing tired of answering my questions?"
  End Select
lbl_Exit:
  Exit Function
End Function
VBA Script (userform code module):
'Public form data variables.
Public LabelCaption As String
Public UserAnswer As String
'Private form data variables.
Private m_strCaptionPassed As String
Private m_strAnswerReturned As String
Private m_Cancel As Boolean

Private Sub UserForm_Initialize()
  'This procedure is triggered when the form is loaded in memory.
  With Me
    'Method 1.
    .Label1.Caption = ActiveDocument.Variables("txtPassedToForm").Value
    'Method 2.
    .Label2.Caption = p_strCaptionLabel
  End With
lbl_Exit:
  Exit Sub
End Sub

Private Sub UserForm_Activate()
  'This procedure is triggered after the form loads and before it is shown to the user.
  With Me
    'Method 3.
    .Label3.Caption = .LabelCaption
    'Method 4.
    .Label4.Caption = .CaptionLabel
  End With
lbl_Exit:
  Exit Sub
End Sub

Private Sub cmdOK_Click()
  With Me
    'Method 1.
    If Len(.TextBox1.Text) > 0 Then
      ActiveDocument.Variables("txtPassedToCall").Value = .TextBox1.Text
    Else
      ActiveDocument.Variables("txtPassedToCall").Value = " "
    End If
    'Method 2.
    p_strAnswerReturned = .TextBox2.Text
    'Method 3.
    UserAnswer = .TextBox3.Text
    'Method 4.
    m_strAnswerReturned = .TextBox4.Text
    .Hide
  End With
lbl_Exit:
  Exit Sub
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  'Intercept/repurpose Unload if user clicks form "X" close button.
  If CloseMode = 0 Then
    Cancel = True
    m_Cancel = True
    Me.Hide
  End If
End Sub

Public Property Let CaptionLabel(ByVal strDataLet As String)
  m_strCaptionPassed = strDataLet
lbl_Exit:
  Exit Property
End Property

Public Property Get CaptionLabel() As String
  CaptionLabel = m_strCaptionPassed
lbl_Exit:
  Exit Property
End Property

Public Property Get Answer() As String
  Answer = m_strAnswerReturned
lbl_Exit:
  Exit Property
End Property

Public Property Get UserCancel() As Boolean
  UserCancel = m_Cancel
lbl_Exit:
  Exit Property
End Property

If you step through the procedures using the Visual Basic eiditor and the F8 key, it will help you see what is actually going on in the code.

Tip Take Away:

Of the methods demonstrated, the last method is probably the most conventional.  It is fully encapsulated and it employs private data variables in the userform class.

Site Note icon See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

Site Note iconNote: This tips page, illustrations and examples were developed using Word 2003. It is wholly functional with Word 2007/2010.

That's it! I hope you have found this tips page useful and informative.

Share

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!

Click to donate British Pound Sterling                   Click to donate US dollars                   Click to donate EU euros

Search my site or the web using Google Search Engine

Google Search Logo