Form Creation

I'm unsure how to proceed with the following task:
What I'd like to do is to create a class that can if needed create and
display an Excel Userform. I've tried adding a reference to the Forms Object
Library and then creating an object reference of type Userform in my class,
e.g.

dim frmMyForm as Userform

The compiler accepts that but does not accept:

Set frmMyForm = New Userform

In addition, Intellisense does not offer a Show method for frmMyForm
generating an error if I write

Public Sub ShowMyForm()
frmMyForm.Show
End Sub

Can anyone point me to a source of enlightenment?

Thanks
William

Why not just create a custom userform (set name to frmMyForm as an example)
complete with event code and so forth, then you can do: (The code module
associated with a userform is already a class module).


Sub ABC()
Dim frm As frmMyForm
Dim frm1 As frmMyForm
Set frm = New frmMyForm
Set frm1 = New frmMyForm
frm.Caption = "ABCD"
frm1.Caption = "EFGH"
frm.Show
frm1.Show
End Sub


--
Regards,
Tom Ogilvy


"William Barnes" <[email protected]> wrote in message
news:[email protected]...
> I'm unsure how to proceed with the following task:
> What I'd like to do is to create a class that can if needed create and
> display an Excel Userform. I've tried adding a reference to the Forms
Object
> Library and then creating an object reference of type Userform in my
class,
> e.g.
>
> dim frmMyForm as Userform
>
> The compiler accepts that but does not accept:
>
> Set frmMyForm = New Userform
>
> In addition, Intellisense does not offer a Show method for frmMyForm
> generating an error if I write
>
> Public Sub ShowMyForm()
> frmMyForm.Show
> End Sub
>
> Can anyone point me to a source of enlightenment?
>
> Thanks
> William
>
>

Well, I guess what I had in mind was to be able to create a class that could
be more or less self-contained, i.e., not dependent upon other files such as
the file associated with the independently created Userform. I guess there's
not really a good way to do this with VBA. Even if you write code using the
VBIDE reference, you still have to create a separate Userform object within
the VBAProject.
Thanks
William

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Why not just create a custom userform (set name to frmMyForm as an
> example)
> complete with event code and so forth, then you can do: (The code module
> associated with a userform is already a class module).
>
>
> Sub ABC()
> Dim frm As frmMyForm
> Dim frm1 As frmMyForm
> Set frm = New frmMyForm
> Set frm1 = New frmMyForm
> frm.Caption = "ABCD"
> frm1.Caption = "EFGH"
> frm.Show
> frm1.Show
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "William Barnes" <[email protected]> wrote in message
> news:[email protected]...
>> I'm unsure how to proceed with the following task:
>> What I'd like to do is to create a class that can if needed create and
>> display an Excel Userform. I've tried adding a reference to the Forms
> Object
>> Library and then creating an object reference of type Userform in my
> class,
>> e.g.
>>
>> dim frmMyForm as Userform
>>
>> The compiler accepts that but does not accept:
>>
>> Set frmMyForm = New Userform
>>
>> In addition, Intellisense does not offer a Show method for frmMyForm
>> generating an error if I write
>>
>> Public Sub ShowMyForm()
>> frmMyForm.Show
>> End Sub
>>
>> Can anyone point me to a source of enlightenment?
>>
>> Thanks
>> William
>>
>>
>
>

It sounds to me that you are referring to a VB executable that would then do
stuff with Excel via automation. You still have the same issue with the form
though and would be better to create the form in the VB project.

In Excel, you could create an add-in with a form class and do your stuff
from there, but it is a bit difficult trying to guess what your objective
really is.

--

HTH

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


"William Barnes" <[email protected]> wrote in message
news:[email protected]...
> Well, I guess what I had in mind was to be able to create a class that
could
> be more or less self-contained, i.e., not dependent upon other files such
as
> the file associated with the independently created Userform. I guess
there's
> not really a good way to do this with VBA. Even if you write code using
the
> VBIDE reference, you still have to create a separate Userform object
within
> the VBAProject.
> Thanks
> William
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Why not just create a custom userform (set name to frmMyForm as an
> > example)
> > complete with event code and so forth, then you can do: (The code
module
> > associated with a userform is already a class module).
> >
> >
> > Sub ABC()
> > Dim frm As frmMyForm
> > Dim frm1 As frmMyForm
> > Set frm = New frmMyForm
> > Set frm1 = New frmMyForm
> > frm.Caption = "ABCD"
> > frm1.Caption = "EFGH"
> > frm.Show
> > frm1.Show
> > End Sub
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "William Barnes" <[email protected]> wrote in message
> > news:[email protected]...
> >> I'm unsure how to proceed with the following task:
> >> What I'd like to do is to create a class that can if needed create and
> >> display an Excel Userform. I've tried adding a reference to the Forms
> > Object
> >> Library and then creating an object reference of type Userform in my
> > class,
> >> e.g.
> >>
> >> dim frmMyForm as Userform
> >>
> >> The compiler accepts that but does not accept:
> >>
> >> Set frmMyForm = New Userform
> >>
> >> In addition, Intellisense does not offer a Show method for frmMyForm
> >> generating an error if I write
> >>
> >> Public Sub ShowMyForm()
> >> frmMyForm.Show
> >> End Sub
> >>
> >> Can anyone point me to a source of enlightenment?
> >>
> >> Thanks
> >> William
> >>
> >>
> >
> >
>
>