Passing Arguments to a Userform

The Initialize event of userforms doesn’t take an argument, so you can’t pass data directly to it. Here are three ways to pass a variable to a userform.

Global Variable

I avoid using global variables whenever practical, but depending on the complexity of the application, it may be the best choice. Create a global variable in a standard module and set it before showing the form.

Public gsMyVariable As String

Sub ShowForm()

    gsMyVariable = “Pass to form”
    UserForm1.Show
    
End Sub

Then you can use that variable in the userform’s code

Private Sub CommandButton1_Click()

    MsgBox gsMyVariable

End Sub

The Tag Property

If you’re not using the Tag property for something else, you can store some data in it for later use. You just need to load the form before you set the property, then show it.

Sub ShowFormTag()

    Load UserForm2
    UserForm2.Tag = “Pass to form”
    UserForm2.Show
    
End Sub

Private Sub CommandButton1_Click()

    MsgBox Me.Tag
    
End Sub

Properties

Because a userform is just a class module with a built-in user interface, you can create properties in the form’s module. The form’s module might look like this

Private msMsg As String

Private Sub CommandButton1_Click()
    MsgBox msMsg
End Sub

Property Let MyProp(sText As String)
    msMsg = sText
End Property

and showing the form from a standard module

Sub ShowFormProp()

    Dim frmMyForm As UserForm3
    
    Set frmMyForm = New UserForm3
    frmMyForm.MyProp = “Pass to form”
    frmMyForm.Show
    
End Sub

17 Comments

  1. Jamie Collins says:

    “a userform is just a class module with a built-in user interface”

    This is a good reason for not coding a userform to rely on a public variable i.e. makes it harder to re-use the class/form in another project.

    Jamie (who has never used a public variable).

    –

  2. Matt H says:

    Waitaminute… I’m going to reveal how self-taught I am here:

    What’s wrong with Public variables? (Perhaps subject of a future post, Dick?)

  3. Dick says:

    Matt: As Jamie mentioned it violates the good coding practice of encapsulation, which roughly means that your code should be self-contained. If you have a function, for instance, that uses a public variable, you can’t copy that function into a new project and use it because the new project won’t have that variable. Unlike Jaime, I can’t say that I’ve never used them, but for nontrivial programs, I avoid them like the plague.

    I wish I was well versed in good programming practices enough to make a nice post about them, but I just pick up bits and pieces here and there. I don’t trust myself to get it right. Heck, maybe I’ll do it anyway.

  4. Rich says:

    I wonder if it isn’t helpful to note three different types of variables that John Walkenbach identifies, referring to the three levels of variable “scope”:

    “You’re talking about a variable’s scope. There are three levels of scope: local, module, and public. Local variables have the narrowest scope and are declared within the procedure. A local variable is visible only to the procedure in which it was declared. Module-level variables are declared at the top of the module, prior to the first procedure. Module-level variables are visible to all procedures in the module. Public variables have the broadest scope, and they are declared using the Public keyword.” (John Wlakenbach, “Excel 2002 Power Programming with VBA”, p. 839)

    This helps me keep straight the distinction between module-level and Public variables, noting that module-level may be as “high” up as I ever need to go.

  5. Rich says:

    An additional comment by Walkenbach explains the advantage of the local variable:

    “In general, local variables are the most efficient because VBA frees up the memory they use when the procedure ends.” (“Excel 2002 Power Programming with VBA”, p. 188)

  6. wiki says:

    I like to work with C, but i work i must do it in vBasic. Passing arguments to forms is great, but how to make form to return value back to his creator? Thanks.

  7. Andy Miller says:

    Passing arguments to forms is great, but how to make form to return value back to his creator?

    This could be a post/thread in itself. The basic answer is that you need to add a Get statement and then access it as a property of the class like you would any other class/control/form (e.g. frmEmployees.Caption, frmEmployees.txtLastName.Text, or clsEmployee.LastName).

  8. Greg F says:

    Here is a way I have used that is a bit unconventional. In fact, I have never seen anybody do this. I am wondering if I am missing some potential pit falls.

    ******************** Userform1 *******************
    Option Explicit
    Dim LocalVar as String

    Private Sub UserForm_Initialize()
    ‘ Initialization code here
    End Sub

    Sub MyVariable (ByVal MyVal as String)
    LocalVar = MyVal
    Userform1.Show
    End Sub

    **************************************************

    To pass the variable to the Userform I call it with:

    Sub MainPgrm()
    Dim MyVal as String
    MyVal = “Some text”

    Userform1.MyVariable MyVal

    End Sub

    The call first executes the “Private Sub UserForm_Initialize()”. It then goes to the “MyVariable” sub routine where the value is assigned to a local variable. If you don’t precede the call to MyVariable with Userform1 it doesn’t work. Is there a reason I should not pass variables to a userform this way?

  9. Jon Peltier says:

    Greg -

    The “official” way to do this, which isn’t much different than what you’re doing, is to use a property procedure in the user form (or other class module). Then use a userform.show command in the calling procedure. There are property let (or set) procedures to assign values to a form’s property, and property get procedures to retrieve these properties from a form. I’ve written a short article on the topic:

    http://peltiertech.com/Excel/PropertyProcedures.html

    - Jon
    —–
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______

  10. Rembo says:

    If you have to use multiple variables or if you want to save their values along with your workbook you might as well use a worksheet to store values. Simply use a cell as a variable. If you want you can name cells or ranges as well, whatever suites you best. This is used for example to create/store menu bars.

    A small example:

    *** workbook ***
    Private Sub Workbook_Open()
    Dim i As Integer, i2 As Integer
    Dim bWksExists As Boolean
    i2 = Worksheets.Count
    For i = 1 To i2
    If Worksheets(i).Name = “wksMyVars” Then
    bWksExists = True
    End If
    Next i
    If Not bWksExists Then
    Worksheets.Add After:=Worksheets(i2)
    With Worksheets(i2 + 1)
    .Name = “wksMyVars”
    .Visible = xlHidden
    End With
    End If
    End Sub

    *** In a module ***
    Sub ShowForm()
    With Worksheets(“wksMyVars”)
    .Range(“A1?).Value = “Greeting”
    .Range(“B1?).Value = “Hello World”
    End With
    ‘ (A1 is set to Greeting for readability of the worksheet itself)
    UserForm1.Show
    End Sub

    *** UserForm1.CommandButton1 ***
    Private Sub CommandButton1_Click()
    Dim sGreeting As String
    sGreeting = Worksheets(“wksMyVars”).Range(“B1?).Value
    MsgBox sGreeting
    End Sub

    Rembo

  11. Erik Eckhardt says:

    ‘Create a new workbook. Add a module with the following code:
    ‘—————
    Option Explicit

    Sub FormVariablePassingExample()
    Dim F As frmValuePass ‘just creates a variable, doesn’t instantiate anything
    Set F = New frmValuePass ‘instantiates the form, but because it’s hidden, code execution continues
    F.Value1 = 8 ‘set some values
    F.Value2 = 6
    ‘You don’t have to have one property per value. You could use an array. Or a publicly defined
    ‘ type (like a C structure: Type TypeName // Variables // End Type.
    ‘ or an object. Or a delimited string. Whatever.

    F.Display ‘This is a custom method on the form which gets it ready and does the .show
    ‘You could use .show directly, but I used this method to give the form a chance
    ‘ to know I was done passing values in so it could do some setup.
    ‘Code execution suspends here until the form is hidden (becomes invisible) again.
    ‘Clicking OK on the form makes the form hide, thereby resuming code execution.

    Debug.Print F.Result ‘we can look at the form or get values out to our heart’s content
    ‘Note that the value printed out is whatever was in the textbox when you clicked close.
    ‘It’s good practice to use something like this Result property instead of examining the
    ‘ textbox directly. What if you change how your form functions or does its job? Better
    ‘ to observe the “Law of Demeter” and only use properly defined interfaces to the form’s
    ‘ data, instead of depending on knowing details about what’s inside the form.

    Set F = Nothing ‘now actually unload the form, by removing all references to it.
    End Sub

    ‘ Next, create a new form called frmValuePass
    ‘ give it any caption you like.
    ‘ Add a textbox and name it txbResult
    ‘ Add a button called btnOK and make it the Default button
    ‘ Add the following code:
    ‘—————

    Option Explicit

    Private gValue1 As Long
    Private gValue2 As Long

    Public Property Let Value1(TheValue As Long)
    gValue1 = TheValue
    End Property

    Public Property Let Value2(TheValue As Long)
    gValue2 = TheValue
    End Property

    Public Sub Display()
    txbResult = gValue1 * gValue2
    Me.Show
    End Sub

    Public Property Get Result() As Long
    Result = Val(txbResult)
    End Property

    Private Sub btnOK_Click()
    Me.Hide
    End Sub

  12. Craig Harrison says:

    I have a two userform system, I want to pass a name from one userform to another, the name is filled in a txt box and used in the userform1 to fill in details to a word document and then i want to pass it to userform2 to be used as part of an output string depending upon choices made in userform2. How would i do this?
    I just need to know how i’d declare the variable and pass it over.

  13. Craig Harrison says:

    NVM I just declared it in the word document coding were i show the first form and its working fine when i set it to public, not exactly ideal coding but it works and its work computers so no memory problems

  14. Marty Ratcliff says:

    Erik Eckhardt’s succinct example (March 3, 2006) of passing multiple parameters to a form via form properties was exactly what I was looking for when nothing else seemed to explain the sequence of events that take place during communication between sub and form module’s “properties” and the form itself.

    After setting up his example, I followed breakpoints throughout execution of the program and plotted 12 steps that occur starting with the “Set F = New frmValuePass” and ending with “Set F = Nothing” and suddenly the light came on. I sketched it out on paper and then applied same structure to my own code & form and within an hour had accomplished what I’d struggled with for about 2 days.

    My problem seemed to be figuring out how to load up a listbox of a form prior to showing the form but where the listbox’s “AddItem” logic needed some passed arguments from my main sub in order to know what to add to the listbox, and needed other passed arguments to be merely displayed on the form once shown (text box, labels, etc.). I kept trying to figure out how to use a form’s “initialize” routine to invoke the ListBox logic, which was of course way too soon IF one needs to 1st pass several arguments to the form’s environment.

    Just in case someone else reading thru this is struggling as I was with the issue of passing multiple arguments to a form via properties (the “recommended” method) prior to the form’s execution, I’ll try to enunciate the key points that were eluding me, a 1st-time form-with- listbox user (some of this is spelled out in Erik’s self-documented code):

    [This will all make much more sense if you code up Erik’s example — including every single one of his embedded comments — and then set breakpoints at everything in the controlling sub and everything in the form’s property routines and subs and then monitor the code as it executes. You can read his comments during breakpoint pauses and make your own notes and likely not even bother with anything else I have to say below.]

    1) It helped when I categorized the “sequential events” in 3 visual columns:
    a) Sub events (controlling sub)
    b) Form Properties & Form Sub events
    c) Form itself
    The “sequential events” will zig-zag from column-to-column during execution

    2) The form doesn’t really require an “initialize” routine prior to passing it some property values. As Erik pointed out, mere instantiation is enough in the sub’s “Set F = New frmValuePass” statement. Turns out I didn’t need to execute “ListBox AddItem” logic until immediately prior to the “Show” command.

    3) The sub’s invoking of a specific property’s “Let” routine in the form module will regain control of events immediately after the “Let” routine is executed. i.e., the controlling sub’s “F.Value1 = 8? invokes the corresponding “Let” routine in the form module at which point control returns to the sub. And realize that this can be done repeatedly as many times as needed by the controlling sub (hence multiple arguments passed to the form).

    4) Realization that by using the “Let” properties to “hold” the passed argument in a separately-named argument, the named argument (and not the property name itself) is where the passed argument will be made available later down the road in your form’s logic –WITHOUT a requirement to use a GET routine. i.e., “gValue1 = TheValue” in Erik’s “Let Value1? routine stages the passed argument in “gValue1? which demonstrates ‘persistence’ within the form’s environment as long as the form remains ‘loaded’.

    5) Realization that from my controlling sub I could invoke a named “form sub” by merely referencing it by name as if the name were a form’s “property”. In Erik’s example, he used “F.Display” in his controlling sub to invoke the form’s “Public Sub Display()” routine. I didn’t know I could do that. It enabled me to put into my own project’s “F.Display” routine a call to my ListBox logic residing in yet a separate sub also behind the form.

    6) And of course, the final objective in my case was to have my controlling sub obtain knowledge of the selected listbox item. And the key there is to not unload the form quite yet when you’re finished with listbox item selection but merely hide it for a few more seconds until control passes from form back to your controlling routine at which point you invoke one final request of the form environment — the “Get Result” property in Erik’s example, where “Result” merely equates itself to any of the previously-used named values from the form routines (txbResult in Erik’s example).

    In summary, the light that came on for me was the realization that I could separately pass named property arguments from my controlling routine to the form as many times as necessary prior to invoking a form routine that could subsequently execute additional “form” logic (e.g., find specific data values from a table and load them into the listbox contents) while making use of the previously-passed arguments to help in that effort, and then actually show the form.

    Hope that helps. And a Huge Thank You to Erik!

  15. Tushar Mehta says:

    Marty: For a different take on the subject see
    Userform Interface Design
    http://www.tushar-mehta.com/publish_train/book_vba/09_userinterface.htm

  16. Bhavik says:

    Hi,

    i am completely new to VB. with some help i have created a userform with some command buttons on it & i have also inserted a Combobox with drop down option from the same worksheet but different workbook.the dropdowm options are basically states of the US.

    now i want to input an excel in the userform, that should populate data from a worksheet in the same workbook.

    based on the state selection in the dropdown of the combobox, in the excel sheet i need to populate the data related to that particular state from column A to Column BI

    i am not able to get an excel sheet in the user form, and please let me know how do i get it related to the dropdown option.

    Any help would be greatly appreciated.

    Thanks in advance.

  17. […] If we wish to pass a value to a second form afterwards see here for a few options: http://dailydoseofexcel.com/archives/2004/07/22/passing-arguments-to-a-userform/ […]

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: