How to Load Form using Variable for Name

I have a project I'm working on that will have lots and lots (20-30 or
more) UserForms. As such, I don't want to pre-load them. Instead, I
want to load them as needed. To do so, I've created a combo box on
each form that lets the user choose what form to go to. The User
picks one from the list then clicks on "OK" button. I then want to
load that form and show it. To make it one bit harder my forms are
all named "frm" + Form name.

For example the forms are named, frmMyForm, frmYourForm, frmHisForm
and the combo box shows "MyForm", "YourForm", "HisForm"

The code I have so far for the OK click routine is:

Private Sub cmdChoose_Area_Click()
vChosen = Me.cmbChoose_Area.Value
vName = "frm" + vChosen

Call FormGoTo(Me.Name, vName)
End Sub

In FormGoTo I have the receiving parameters (vFormFrom, vFormTo)

No matter what I do I can't Load "vFormTo".

I've tried
FormGoTo (vFormFrom, vFormTo as UserForm)

but then I get an error from the calling procedure.

I've tried:
Load vFormTo
Load UserForms(vFormTo)
etc, but with no luck.

Is there any way to do this?

Doug
Tue Sep 16 21:52:00 PDT 2008

There may be a better way, but you could create a subroutines to load each
form and call the appropriate subroutine based on the item selected from the
combobox.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"= Stuart =" <skarden@gmail.com> wrote in message
news:f2eb8f1f-f3ce-4410-8084-56be1b795d72@2g2000hsn.googlegroups.com...
>I have a project I'm working on that will have lots and lots (20-30 or
> more) UserForms. As such, I don't want to pre-load them. Instead, I
> want to load them as needed. To do so, I've created a combo box on
> each form that lets the user choose what form to go to. The User
> picks one from the list then clicks on "OK" button. I then want to
> load that form and show it. To make it one bit harder my forms are
> all named "frm" + Form name.
>
> For example the forms are named, frmMyForm, frmYourForm, frmHisForm
> and the combo box shows "MyForm", "YourForm", "HisForm"
>
> The code I have so far for the OK click routine is:
>
> Private Sub cmdChoose_Area_Click()
> vChosen = Me.cmbChoose_Area.Value
> vName = "frm" + vChosen
>
> Call FormGoTo(Me.Name, vName)
> End Sub
>
> In FormGoTo I have the receiving parameters (vFormFrom, vFormTo)
>
> No matter what I do I can't Load "vFormTo".
>
> I've tried
> FormGoTo (vFormFrom, vFormTo as UserForm)
>
> but then I get an error from the calling procedure.
>
> I've tried:
> Load vFormTo
> Load UserForms(vFormTo)
> etc, but with no luck.
>
> Is there any way to do this?
>

Jonathan
Wed Sep 17 02:33:44 PDT 2008


"= Stuart =" <skarden@gmail.com> wrote in message
news:f2eb8f1f-f3ce-4410-8084-56be1b795d72@2g2000hsn.googlegroups.com...
>I have a project I'm working on that will have lots and lots (20-30 or
> more) UserForms. As such, I don't want to pre-load them. Instead, I
> want to load them as needed. To do so, I've created a combo box on
> each form that lets the user choose what form to go to. The User
> picks one from the list then clicks on "OK" button. I then want to
> load that form and show it. To make it one bit harder my forms are
> all named "frm" + Form name.
>
> For example the forms are named, frmMyForm, frmYourForm, frmHisForm
> and the combo box shows "MyForm", "YourForm", "HisForm"
>
> The code I have so far for the OK click routine is:
>
> Private Sub cmdChoose_Area_Click()
> vChosen = Me.cmbChoose_Area.Value
> vName = "frm" + vChosen
>
> Call FormGoTo(Me.Name, vName)
> End Sub
>
> In FormGoTo I have the receiving parameters (vFormFrom, vFormTo)
>
> No matter what I do I can't Load "vFormTo".
>
> I've tried
> FormGoTo (vFormFrom, vFormTo as UserForm)
>
> but then I get an error from the calling procedure.
>
> I've tried:
> Load vFormTo
> Load UserForms(vFormTo)
> etc, but with no luck.
>
> Is there any way to do this?
>

What is the declaration line of routine FormGoTo?


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

=
Wed Sep 17 03:52:05 PDT 2008

On Sep 17, 5:33=A0am, "Jonathan West" <jw...@mvps.org> wrote:
> "=3D Stuart =3D" <skar...@gmail.com> wrote in messagenews:f2eb8f1f-f3ce-4=
410-8084-56be1b795d72@2g2000hsn.googlegroups.com...
>
>
>
> >I have a project I'm working on that will have lots and lots (20-30 or
> > more) UserForms. =A0As such, I don't want to pre-load them. =A0Instead,=
I
> > want to load them as needed. To do so, I've created a combo box on
> > each form that lets the user choose what form to go to. =A0The User
> > picks one from the list then clicks on "OK" button. =A0I then want to
> > load that form and show it. =A0To make it one bit harder my forms are
> > all named "frm" + Form name.
>
> > For example the forms are named, frmMyForm, frmYourForm, frmHisForm
> > and the combo box shows "MyForm", "YourForm", "HisForm"
>
> > The code I have so far for the OK click routine is:
>
> > Private Sub cmdChoose_Area_Click()
> > =A0 =A0vChosen =3D Me.cmbChoose_Area.Value
> > =A0 =A0vName =3D "frm" + vChosen
>
> > =A0 =A0Call FormGoTo(Me.Name, vName)
> > End Sub
>
> > In FormGoTo I have the receiving parameters (vFormFrom, vFormTo)
>
> > No matter what I do I can't Load "vFormTo".
>
> > I've tried
> > FormGoTo (vFormFrom, vFormTo as UserForm)
>
> > but then I get an error from the calling procedure.
>
> > I've tried:
> > Load vFormTo
> > Load UserForms(vFormTo)
> > etc, but with no luck.
>
> > Is there any way to do this?
>
> What is the declaration line of routine FormGoTo?
>
> --
> Regards
> Jonathan West - Word MVPwww.intelligentdocuments.co.uk
> Please reply to the newsgroup

It is:
Sub FormGoTo(vFrom, vTo)

I've tried
Sub FormGoTo(vFrom, vTo as UserForm)

but then I got an error from the calling procedure:
Call FormGoTo(Me.Name, vTo)

I tried:
Call FormGoTo(Me.Name, vTo as UserForm)
but that didn't work as vTo is a string

Right now, the kinda brute force workaround I came up with is using a
Select Case in FormGoTo like this:

Select Case (vTo)
Case "MyForm"
frmMyForm.Show
Case "YourForm"
frmYourForm.Show
Case "HisForm"
frmHisForm.Show
End Select

It works, but to say the least is less than elegant and will get
cumbersome as I get more and more forms.