Passing a variable from a UserForm to a Procedure

[Herve]

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 variable in a cell on a worksheet before unloading the UserForm, then read it from that location when I am back in the Procedure. I figure there must be an easier way

Thanks!

 

[Jon Peltier]

Herve -

Not sure what you mean by a Control Button variable.

What I do is load the userform from the main procedure, but keep it hidden. I use code in the main procedure to initialize the userform, then finally use UserForm1.Show in the main code to show the form. The user can check checkboxes and enter text, etc. When finished, the button that the user clicks merely hides the form, and the main code can get whatever values it wants from the controls on the form. When it has harvested all the information it needs, then it unloads the form.

No global variables needed, and the main procedure remains in control the entire time.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[Robert2100]

I also do what Jon does.

Sometimes If I'm using lot's of forms and collecting variables from here and there, I'll create an array and store the variables in the array (changing it's size as I go). I then pass this array to whatever subroutine is running when I call the subroutine. This allows me to close forms with crucial data. But perhaps I'm just a neat freak.

 

[Herve]

Thanks Jon and Robert.
I must be doing something fundamentally wrong...

Let's say I am at a point in a procedure where I need the user to choose among ten values. I therefore load them in a ListBox in a UserForm (using an .AddItem method for instance), then I show the UserForm (UserForm.Show vbModal) which passes control to the UserForm.

The user selects Value(x) in the ListBox then clicks on the "OK" ControlButton1, whose code is:

Sub ControlButton1_Click()
User_Choice = ListBox.Value
Unload UserForm
End Sub

I am now back in my main procedure. How do I grab User_Choice which is a variable defined in the UserForm?

This is the question

Thanks again for your time.

 

[Richie(UK)]

Hi Herve,

Option1 - a Public variable (this must be in a general module, not the userform).

General Module:

Public User_Choice
'Declare Public variable in general module

Sub ShowUF()
UserForm1.Show
MsgBox User_Choice
End Sub

Userform code:

Private Sub CommandButton1_Click()
User_Choice = ListBox1.Value
Unload UserForm1
End Sub

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

Option2 - avoid use of Public variable (preferred).

General module:

Sub ShowUF()
UserForm1.Show
MsgBox UserForm1.ListBox1.Value
Unload UserForm1
'unload it now that info obtained
End Sub

Userform 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

HTH

 

[Herve]

Richie,

This is indeed the solution: UserForm1.Hide

Thanks a lot for for the detailed answer

 

[Herve]

Using this method, I need to use OptionButtons to know the choices selected, correct? (I am not talking about a list anymore.)

Is there a way to know if a CommandButton has been pressed (something like "If Button_Click is True" from the main code, the way I can read "If OptionButton is True" before unloading the UserForm as suggested above) ?

Reason: When the user can only select one choice, I think it's easier for him/her to press a single button rather than choose one option, then click on OK.)

To make myself clear, let's say I ask "Black" or "White."
I can use 2 OptionButtons and a CommandButton. Works fine. However, my preference so far has been to use 2 CommandButtons which save the choice AND close the UserForm at once. Obviously, as indicated first in this thread, I need to save the choice in a temporary cell.

Is there a way to do that?

Thanks!

 

[Jon Peltier]

Hi Herve -

You can use code something like this in the userform:

Public TheColor as String

Private Sub CommandButtonBlack_Click()
TheColor = "Black"
Me.Hide
End Sub

Private Sub CommandButtonWhite_Click()
TheColor = "White"
Me.Hide
End Sub

And in the general code module:

Sub GetColorChoice()
Dim MyColor as String
UserForm1.Show
MyColor = UserForm1.TheColor
Unload UserForm1
MsgBox MyColor
End Sub

Of course, you still have the public variable, TheColor, in the userform. But you could replace this with a visible or invisible textbox on the form that stores the string "Black" or "White".

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[Herve]

Jon,

I love the invisible TextBox

For those interested:

UserForm:

Private Sub Black_Click()
TextBox.Value = "Black"
Me.Hide
End Sub

Private Sub White_Click()
TextBox.Value = "White"
Me.Hide
End Sub

Main Code:

Sub GetColorChoice()
Dim MyColor As String
UserForm.Show
MyColor = UserForm.TextBox.Value
Unload UserForm
MsgBox MyColor
End Sub

Thanks a lot, this is very neat!

 

[Jon Peltier]

Herve -

I use invisible text boxes and check boxes all the time. Very handy and very sneaky.

- Jon