Dear All

I am experiencing something of an impasse with my VB when
I try to show a form from an array of form names. My
initial attempts have revolved around the following code:

Run_Form_Array = Array
("New_RPI_Data_Frm", "Surveyor_Info_Frm", "Bank_Debt_Frm",
"Bond_Units_Frm", "Stock_Performance_Frm")

Form_Select = Run_Form_Array(Selection)
Unload Input_Menu_Frm
UserForm.Show (Form_Select)

I expect that it is the manner in which I either collect
or retrieve the names for / from the array.

Matthew

I haven't used the usrforms collection, but it behaves
quite oddly. Seems that you can only pass an index rather
than a name for the form, and the index doen't always (
well never) match the order forms are loaded, unless
they're all unloaded first!

I created five forms, and set a change event on a cell
that loads the form whose number ( between 1 and 5 ) I
enter. This works fine

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
OpenForm Target.Value
End If
End Sub

Sub OpenForm(FormNumber As Long)

Dim forms
LoadAllForms
'forms = Range("A1:A5")
UserForms(FormNumber - 1).Show


End Sub
Private Sub LoadAllForms()
Unload UserForm1
Unload UserForm2
Unload UserForm3
Unload UserForm4
Unload UserForm5

Load UserForm1
Load UserForm2
Load UserForm3
Load UserForm4
Load UserForm5

End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----

Dear All

I am experiencing something of an impasse with my VB
when
I try to show a form from an array of form names. My
initial attempts have revolved around the following code:

Run_Form_Array = Array

("New_RPI_Data_Frm", "Surveyor_Info_Frm", "Bank_Debt_Frm",

"Bond_Units_Frm", "Stock_Performance_Frm")

Form_Select = Run_Form_Array(Selection)
Unload Input_Menu_Frm
UserForm.Show (Form_Select)

I expect that it is the manner in which I either collect
or retrieve the names for / from the array.

Matthew
.

Patrick

Thank you but it sounds as though I would be in an
improved position if instead of running that form with
Option_Buttons I used Control_Buttons. Unfortunately,
this means that a User can no longer reconsider or correct
a mistake.

Matthew

-----Original Message-----
I haven't used the usrforms collection, but it behaves
quite oddly. Seems that you can only pass an index rather
than a name for the form, and the index doen't always (
well never) match the order forms are loaded, unless
they're all unloaded first!

I created five forms, and set a change event on a cell
that loads the form whose number ( between 1 and 5 ) I
enter. This works fine

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
OpenForm Target.Value
End If
End Sub

Sub OpenForm(FormNumber As Long)

Dim forms
LoadAllForms
'forms = Range("A1:A5")
UserForms(FormNumber - 1).Show


End Sub
Private Sub LoadAllForms()
Unload UserForm1
Unload UserForm2
Unload UserForm3
Unload UserForm4
Unload UserForm5

Load UserForm1
Load UserForm2
Load UserForm3
Load UserForm4
Load UserForm5

End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----

Dear All

I am experiencing something of an impasse with my VB
when
I try to show a form from an array of form names. My
initial attempts have revolved around the following code:

Run_Form_Array = Array

("New_RPI_Data_Frm", "Surveyor_Info_Frm", "Bank_Debt_Frm",

"Bond_Units_Frm", "Stock_Performance_Frm")

Form_Select = Run_Form_Array(Selection)
Unload Input_Menu_Frm
UserForm.Show (Form_Select)

I expect that it is the manner in which I either collect
or retrieve the names for / from the array.

Matthew
.

.

A work-around is to have each form save & load data from a
worksheet. You can keep this hidden

Another solution thaty I've used is instead of multiple
sheets, use a tabbed form. I also use frames . . .hiding
a frame hides any control within the frame.
If you had 5 frames on one form, you could make it appear
to be 5 forms by setting all the frames visible
property's to false, then making the frame that you meed
visible on loading....

on a userform drop a few frames, add some labels to help
identify them - say one in each frame. set each frame's
visible property to false, add this to the useform's code
page:-

Public Sub SHowFrame(Frame As Long)
On Error Resume Next
Controls("Frame" & Frame).Visible = True
End Sub

Run the form - you' ll see a blank form.
add a standard module withthis code :-
Sub Test()
Load UserForm1
UserForm1.SHowFrame 2
UserForm1.Show
End Sub

run th eTest procedure...you'll see the form show with
the 2nd frame vivible


Hope this gives you some useful ideas

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----

Patrick

Thank you but it sounds as though I would be in an
improved position if instead of running that form with
Option_Buttons I used Control_Buttons. Unfortunately,
this means that a User can no longer reconsider or
correct
a mistake.

Matthew

-----Original Message-----
I haven't used the usrforms collection, but it behaves
quite oddly. Seems that you can only pass an index
rather
than a name for the form, and the index doen't always (
well never) match the order forms are loaded, unless
they're all unloaded first!

I created five forms, and set a change event on a cell
that loads the form whose number ( between 1 and 5 ) I
enter. This works fine

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$2" Then
OpenForm Target.Value
End If
End Sub

Sub OpenForm(FormNumber As Long)

Dim forms
LoadAllForms
'forms = Range("A1:A5")
UserForms(FormNumber - 1).Show


End Sub
Private Sub LoadAllForms()
Unload UserForm1
Unload UserForm2
Unload UserForm3
Unload UserForm4
Unload UserForm5

Load UserForm1
Load UserForm2
Load UserForm3
Load UserForm4
Load UserForm5

End Sub


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----

Dear All

I am experiencing something of an impasse with my VB
when
I try to show a form from an array of form names. My
initial attempts have revolved around the following
code:

Run_Form_Array = Array


("New_RPI_Data_Frm", "Surveyor_Info_Frm", "Bank_Debt_Frm",

"Bond_Units_Frm", "Stock_Performance_Frm")

Form_Select = Run_Form_Array(Selection)
Unload Input_Menu_Frm
UserForm.Show (Form_Select)

I expect that it is the manner in which I either
collect
or retrieve the names for / from the array.

Matthew
.

.

.