Userform Question (Load/Unload/Show/Hide)

[RPIJG]

I want to be able to navigate between two userforms (seperate)

The first Userform is the chooser it basically has a combobox and when
the user selects from the Combobox the second userform loads, and the
first is unloaded. That seems to work fine. However, if the user has
chosen the wrong selection in the first userform and wants to go back
to the first, it overlaps and doesn't unload the second userform,
However, there are going to be multiple userforms accessed from that
first userform and it won't unload what isn't loaded (as it should be),
and a simple If...Then statement didn't work because I used mismatched
types. Here is kind of what I've got.

Open the workbook and Userform1 shows up.
(User selects entry from Combobox).
Userform2 loads and upon initializing Unloads Userform1.
(User clicks command button to return to Userform1)

Here either two things happen, A)Hell breaks loose, or B) not what I
want it to do.

I tried using

If Userform2.Show Then Unload Userform2 End If

But I know that isn't right because I'm mixing things and because Excel
threw a fit at me. Thanks for any help.

 

[Bob Phillips]

Bit difficult to answer specifically, but here is an example

Userform1

Private Sub ComboBox1_Change()
UserForm2.mySelect = ComboBox1.Value
Me.Hide
UserForm2.Show
End Sub

Private Sub UserForm_Activate()
With Me.ComboBox1
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
End With
End Sub

Userform2

Public mySelect

Private Sub CommandButton1_Click()
MsgBox mySelect
Unload Me
UserForm1.Show
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RPIJG]

Thanks for the help Bob, this didn't seem to work for me, I think
because I have so many userforms that can be accessed from Userform1

The overlap is fine with me, but is there a way I can unload all
userforms that are loaded in one fell swoop.


Can I dim all the userforms as one thing and then unload all of them
even if some aren't loaded or will that throw an error.

or is there a way I can have excel only unload the userforms that are
loaded?

 

[Bob Phillips]

The userforms collection only contains forms that are loaded, not all forms
in the project, so iterating through that collection will do what you want.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RPIJG]

Anyone please help

 

[RPIJG]

Anyone please help

 

[dok112]

So, you have a userform that is a "selector" userform, and based on th
combobox selection on that form, another form will initialize, correct
If the wrong selection is made, you want to go back to the origina
userform? Why not put a commandbutton on the 2nd userform that wil
hide the current userform and re-open userform1.

sub commandbutton1_click ()

me.hide
userform1.show

end sub

or a macro that closes all userforms, and then assign that to a comman
button...and after that's done initialize the first form again w
userform1.show...

sub form_close ()
userform1.hide
userform2.hide
userform3.hide
etc...
end sub

if you're looking for something different, let me know, and I'll see i
I understand better

 

[RPIJG]

I tried what you are saying but it wasn't working... I kept gettin
errors saying things couldn't be opened because they hadn't bee
unloaded. And secondly because not everything is shown, or loaded o
startup. So it won't hide what isn't loaded already. And I'm not sur
I want it to load all of these on startup either

 

[dok112]

ok, so in the beginning of the command type the following...

On Error Resume Next

This will tell Excel to bypass the error of it being unloaded.

Sub test()
On Error Resume Next
UserForm1.Hide
UserForm2.Hide
UserForm3.Hide
UserForm4.Hide
End Su

 

[RPIJG]

That should do it thanks