Program Control - Gotta Be A Better Way
 

In a VBA program I load a userform with buttons and combo boxes,
populate the combo boxes, and then show the form.

The main subroutine seems to pause execution while the userform is
visible, then resumes when the form is hidden. That seems a little
weird to me. I mean, what if I wanted the program to continue? What
if I wanted it to be reading a whole bunch of data or doing
calculations while the user thought about his choices? Is there a way
to force it to continue? But that's not the main question.

On the userform I have buttons for continue and exit. Continue simply
hides the userform. (I can't unload it or else I lose the values
chosen in the combo boxes.) I have to have exit do something else.

Right now, what I do is have the exit command button set one of the
combo box values to an impossible value. Then when control goes back
to the main subroutine, if the value is that, I unload the userform and
exit sub.

Two questions on this:

1) Is there a way to pass a variable without it being a value on the
userform? Obviously, I could create a text box on the form, make it
invisible, and use that, but it's the same effect. There is probably
some way to make a variable "global" or "public" but how do I do that?

2) Is there a way to do this without passing variables. Can I have the
Exit button just terminate the main subroutine?

The outline of the code appears below. Thanks.

____________________________________
Code behind Sheet1:

Private sub mainsub_click()
load userform1
userform1.combobox1.additem 1
userform1.combobox1.additem 2
userform1.show
if userform1.combox1.value = -1 then exit sub
msgbox "The value of userform1 combo box is" &
userform1.combobox1.value
end sub


Code behind userform1:

Private sub continue_click()
userform1.hide
end sub

Private sub exit_click()
userform1.combobox1.value = -1
userform1.hide
end sub

Program Control - Gotta Be A Better Way
 

A userform has a Tag property (a string) along with most (all) of the
controls you can put on a form. You can generally put any value in
a Tag and then extract it later... x = Clng(UserForm1.TextBox1.Tag).

Yes, code execution stops when a userform is shown. However,
code in the userform module can execute... for instance the Initialize
Event for the form or the click events for controls.

A userform should be Unloaded and Set to Nothing when you are
done with it. Hiding a userform does not remove it from memory.
However, if you want to display a form more than once, it is usually
best to just hide the form and then Show it every time you need it.
When finally done, then unload it.
Your button code could look something like this...

Private sub exit_click()
Me.Tag = "Cancel"
Me.Hide
End Sub

Private sub continue_click()
Me.Tag = "Continue"
Me.Hide
End Sub

Then in your main sub...
UserForm1.Show
If UserForm1.Tag = "Cancel Then
Unload UserForm1
Set UserForm1 = Nothing
Exit Sub
End If
'do other stuff if tag is continue
-------------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Hank Youngerman"
wrote in message
In a VBA program I load a userform with buttons and combo boxes,
populate the combo boxes, and then show the form.

The main subroutine seems to pause execution while the userform is
visible, then resumes when the form is hidden. That seems a little
weird to me. I mean, what if I wanted the program to continue? What
if I wanted it to be reading a whole bunch of data or doing
calculations while the user thought about his choices? Is there a way
to force it to continue? But that's not the main question.

On the userform I have buttons for continue and exit. Continue simply
hides the userform. (I can't unload it or else I lose the values
chosen in the combo boxes.) I have to have exit do something else.

Right now, what I do is have the exit command button set one of the
combo box values to an impossible value. Then when control goes back
to the main subroutine, if the value is that, I unload the userform and
exit sub.
Two questions on this:

1) Is there a way to pass a variable without it being a value on the
userform? Obviously, I could create a text box on the form, make it
invisible, and use that, but it's the same effect. There is probably
some way to make a variable "global" or "public" but how do I do that?
2) Is there a way to do this without passing variables. Can I have the
Exit button just terminate the main subroutine?

The outline of the code appears below. Thanks.
____________________________________
Code behind Sheet1:

Private sub mainsub_click()
load userform1
userform1.combobox1.additem 1
userform1.combobox1.additem 2
userform1.show
if userform1.combox1.value = -1 then exit sub
msgbox "The value of userform1 combo box is" &
userform1.combobox1.value
end sub

Code behind userform1:

Private sub continue_click()
userform1.hide
end sub

Private sub exit_click()
userform1.combobox1.value = -1
userform1.hide
end sub