Passing a variable from a UserForm to a Procedure without Unloading

[GeeWhiz7]

A very interesting post a while back from user Herve and replies by Jon Peltier and Richie(UK) talked about initializing a userform from a general module but keeping it hidden so the value could be passed back to the general module.

Passing a variable from a UserForm to a Procedure

Hello All! I do not succeed to pass a variable (Control Button) from a UserForm to a Procedure (in a regular Module). I am declaring the variable as Public and Static, but I get a message that VBA does not support this kind of automation. What's the best way to do that? (I currently save the...

www.mrexcel.com

How can I keep the userform displayed while doing this and display the current listbox.value in a cell in a worksheet instead of the userform unloading and disappearing?

• I would like to have the value of the listbox selection shown in a certain cell and if the selection changes so does the value in the cell.
• Once the user selects the value they want, my intention is to use another button on the userform (action below) to perform an action using the value in that cell.
• If they want to do it again with that value or another, they keep selecting and then pushing action, all the while the form is present.
• I'd like the userform to stay visible until the user pushes a dismiss or 'x' button.

Thanks for the help!

Their code which works to get the one value, but unloads the form and makes it disappear is here.

'General module:
'Code:
Sub ShowUF()
UserForm1.Show
MsgBox UserForm1.ListBox1.Value
Unload UserForm1
'unload it now that info obtained
End Sub
'Userform code:
'Code:
Private Sub CommandButton1_Click()
UserForm1.Hide
'note Hide not Unload
End Sub

Private Sub UserForm_Initialize()
With ListBox1
    .Clear
    .AddItem ("Test1")
    .AddItem ("Test2")
    .AddItem ("Test3")
End With
End Sub

 

[GWteB]

Forgot to respond on this:

Watching or looking at immediate window doesn't show anything in there when I select "Test2".

The Locals window is used for that, the Immediate window will only show any output if the Debug.Print statement is used in the running code.

 

[GeeWhiz7]

I realy had to peek to F1 at GP Canada so a delayed response.



I'll try to explain.

As of our post #5 code, we know there's a custom made ListBoxValue property, which is a member of the UserForm1 object*.
VBA has some built-in implicitness** and when we are trying to "reach" a member of a certain object without being explicit about that certain (parent) object, VBA doesn't recognize that member and treats the given name (in this case ListBoxValue) as a (not yet declared!) variable of the Variant type (and declares it for you). That's what's happening in your Test sub.
If you would have used the Option Explicit directive, VBA would have warned you about a not existing variabele at compile time. Now you end up with an empty ListBoxValue variable.

The DlgOnAction procedure, which is invoked by the Button_Click event handler in your userform, is used to perform the desired action, see attached.
This procedure needs an argument to be passed on (red rectangle) of type UserForm1 (your userform). That argument is used to "reach" to its member named ListBoxValue (blue rectangle).
This DlgOnAction procedure can be expanded with code to perform more actions if required.
Your userform can be expanded with more custom made properties, which will be reachable with this approach.

Finally, unlike both the code you refered to in your post #1 and @EXCEL MAX's code, my code is not sensitive to the modality of the userform.
If you don't want the user to do anything else then interacting with your form as long as it's displayed, you can show it modal. However, if users are allowed to interact with worksheets as well at the same time the form is displayed, you can show the form modeless without the need to modify the code in a drastic way.

Hopefully this is more clear.


View attachment 67442

* For better understanding, in Excel and VBA it's all about Objects, which may have members. Those members come in three types: Methods, Properties, and Events (more details over here).
** More info about VBA's implicitness:

Implicit and Explicit declaration of variables in VBA

VBA allows a great deal of flexibility in declaring variable. Many time this flexibility leads to poor quality of coding. In this article I am going to guide you what kind of practices you should embrace while declaring a variable in VBA. This is a guidance to make you perfect in variable...

learnexcelmacro.com

VBA Data Types - Converting - Implicit

VBA Reference - Microsoft Office Add-ins and Consultancy. One website for all Microsoft Office Users and Developers.

bettersolutions.com

Thank you GWteB, this helps me understand your code and its choices and the missing Option Explicit explains the absent value in my test which I didn't even think about. I also appreciate the references which I will head towards to learn more. On the one hand, I learned how to solve my immediate problem, on the other it reminds me I need to learn a lot more. Thank you!

 

[GWteB]

You are welcome and thanks for posting back

 

[EXCEL MAX]

Geewhiz7, your explanation is much better then my example and exactly what I want to say.
I'm glad you understand so well.