Passing forms information in vba

Question

I have a sub, and in it I define 2 variables, and assign 1 of these strings to a text box in a form I've already created.

Sub test()

   dim x as string
   dim y as string
   x = "hello"
   y = "friends"

   myForm.textbox1.value = x
   myForm.show

End sub

Now that the form is active, and the form is soliciting information, and executing other code based on user input, I remember that my string y is relevant. But I don't need it in a textbox. Which is where I begin to wonder: is there any way that data can be passed to the form so that the form 'can see' activities from the sub test() without assigning it to an object on the form?

DisplayName · Accepted Answer · 2018-08-05T12:57:18.647

Or you can use Tag property of UserForm object

Sub test()

    dim x as string
    dim y as string
    x = "hello"
    y = "friends"

    With myForm
        .Tag = y ‘ “pass” the value of y to the form by storing it into its Tag property
        .textbox1.value = x
        .show
        y = .Tag ‘ assign y the value stored in the form Tag property 
    End With
End sub

This way, in your UserForm code you can retrieve that value as follows:

Dim y As String ‘ being inside the form this is a locally scoped variable
y= Me.Tag ‘ assign “local” y the value stored in Tag property from outside the form
.... ‘ your code
Me.Tag = y ‘ store final value of “local” y to Tag property and make it avalable to the “outer” world

score 2 · Answer 2 · answered Aug 05 '18 at 06:31

A public scope variable will be visible to the form. The value can be set for the variable in the test sub. You want the test sub to run before the form is loaded if you want the value that is assigned within the test sub.

Public/Global variables are declared at the top of the module outside of the sub.

See the discussion here: How do I declare a global variable in VBA?

score 1 · Answer 3 · answered Aug 05 '18 at 08:35

As the other answers suggest you could use a public variable, but I would also suggest that you take a look and maybe use the public property.

Properties are different from public variables because with properties you can execute your own code when the client gets or sets the value.

Take a look at this post here on StackOverflow

Passing forms information in vba

3 Answers3