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.

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

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.

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

and showing the form from a standard module

24 thoughts on “Passing Arguments to a Userform

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

  16. Jamie Collins, you’ve never used a global variable? I am impressed. All hail the elitist jerk!

  17. How to pass a Value to a listbox on another userform without a click() event? … I´ll need to use the value as a filter.

    For example, i have a “clients” table, throught a userform and a listbox i will select a Client, after that open another userform where shows me the invoices of the client for select the row to modify

    thanks

  18. every time a method is executed from the userform class, it auto-initializes the form which is unwanted. Is there a way around this? I basically want to set all my properties I want to pass, then launch/initialize the form.

  19. My take on the subject is this:
    If you want to use an external variable within a userform’s code, then the article’s 3rd way of passing arguments (using propeties, MyProp) is neat and works very well.
    However if you need to, for example, pass text to a userform textbox prior to it opening, then I have found the following to be simple and work very well.
    After creating a simple userform named “MyMessageBox (to replace the default MsgBox), I added a textbox with I named “Text”.

    My calling routine is:

    Sub MyMsgBox()
    MyMessageBox.Caption = “My Caption Text”
    MyMessageBox.Text = “This is the text I want to display”
    MyMessageBox.Show
    End Sub

    As you can see, you can also initiate other properties of the userform prior to opening.

    This can be simplified to the following:

    Sub MyMsgBox()
    With MyMessageBox
    .Caption = “My Caption Text”
    .Text = “This is the text I want to display”
    .Show
    End With
    End Sub

    which has the added advantage of allowing properties to be retrieved from the userform afterwards, provided the data retrieval is done before the “End With” statement:

    Sub MyMsgBox()
    Dim strReply as String
    With MyMessageBox
    .Caption = “My Caption Text”
    .Text = “This is the text I want to display”
    .Show
    StrReply = .TextBox2
    End With
    End Sub

    (“TextBox2” is a second textbox on the MyMessageBox userform).


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.