Class Modules and Userforms

[Stephen Lloyd]

I'm creating a macro that I want to be more object oriented than procedural.

The macro sends out schedules to employees. The user interacts with a
userform to select the groups to which the schedules should go.

I started by declaring the userform directly from my main sub in the
following fashion.

Dim frmUserForm As FUserformm
Set frmUserForm = New FUserForm

Later, since I wanted to use a class module to encapsulate the information
and functionality I thought I should instead declare the userform in the
class module in a similar fashion as above in order to read values from the
userform into properties in the class module.

i.e. I want to access objSchedules.daynum from the main module where daynum
is determined by a date selected on the userform.

1. Should I declare the userform object in the class module or the main
module?
2. If in the class module, do I also need to declare the class in the
userform module to access properties in the class module? How?
3. If in the main module do I simply set the obj properties using form
properties, i.e.
objSchedules.daynum = frmUserFrom.DayofWeek where DayofWeek has it's own
set of Get and Let procedures and the Let is called from the userforms
OK_Click.

I might have a good cry after this. Thanks for any and all help. I have
read extensively on chip pearsons web site and an article on peltier's
website as well as research on ozgrid and MrExcel. Nothing seems to discuss
interaction between custom class modules and userform(class) modules.

 

[Jon Peltier]

I'm creating a macro that I want to be more object oriented than
procedural.

The macro sends out schedules to employees. The user interacts with a
userform to select the groups to which the schedules should go.

I started by declaring the userform directly from my main sub in the
following fashion.

Dim frmUserForm As FUserformm
Set frmUserForm = New FUserForm

Later, since I wanted to use a class module to encapsulate the information
and functionality I thought I should instead declare the userform in the
class module in a similar fashion as above in order to read values from
the
userform into properties in the class module.

i.e. I want to access objSchedules.daynum from the main module where
daynum
is determined by a date selected on the userform.

1. Should I declare the userform object in the class module or the main
module?

If the userform is only accessed through the class module, you may as well
declare it only in the class module.

2. If in the class module, do I also need to declare the class in the
userform module to access properties in the class module? How?

I would declare a module-level object variable of the type FUserform. I
would use a property to assign the variable from the class:

'in the userform module:

Dim mclsWhatever as CWhatever

Public Property Set ParentClass (cls As CWhatever)
Set mclsWhatever = cls
End Property

' in the class module:

Dim frmUserForm As FUserformm

Set frmUserForm = New FUserForm
With frmUserForm
Set .ParentClass = Me
.Show
End With

3. If in the main module do I simply set the obj properties using form
properties, i.e.
objSchedules.daynum = frmUserFrom.DayofWeek where DayofWeek has it's own
set of Get and Let procedures and the Let is called from the userforms
OK_Click.

If the form is encapsulated in the class, you would have to use the class as
an intermediary. The main code interacts with data in the class using
properteis in the class, and the class interacts with data in the form using
properties in the form.

I might have a good cry after this. Thanks for any and all help. I have
read extensively on chip pearsons web site and an article on peltier's
website as well as research on ozgrid and MrExcel. Nothing seems to
discuss
interaction between custom class modules and userform(class) modules.

If the form doesn't need to interact with the class except at its creation
and destruction (i.e., to get data in and out), just do it with properties,
and put the code into the form that you would other wise put into the class.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______

 

[Stephen Lloyd]

Jon,

Excellent. Worked like a charm. I had not considered setting the class as
a property. Thanks for sharing the magic.

Stephen

 

[Jon Peltier]

Magic, I don't know. It's just a little trick I stumbled across a long time
ago. I don't remember if I read it somewhere or even thought of it myself.
It's sure useful, though.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/>
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html
_______