Hi,

I have an class instance on all my worksheets, is there some way I
can dynamically execute a method on these objects? Something like

for each worksheet in worksheets
worksheet.myObject.myMethod
next worksheet

?

In principle you can call a method within a class passing an object to it,
so yes. But the class method would have to accept a worksheet as an
argument. The syntax would be

In the class

Function myMethod (sh As Worksheet)
Msgbox sh.Name
End Function

and in the general module

Set myObject = New class_name

For Each ws In Activeworkbook.Worksheets
MyObject.MyMethod ws
Next ws


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nik" wrote in message
oups.com...
Hi,

I have an class instance on all my worksheets, is there some way I
can dynamically execute a method on these objects? Something like

for each worksheet in worksheets
worksheet.myObject.myMethod
next worksheet

?

Nik,

I'm not sure if this is what you want. You probably need to set up a
collection to hold the instances of your class -- this needs to be set up in
a module -- not a class module. See the following:

***I set up the following Class (named Class1)

--------------------------------------------------------
Dim mTheSheet As Worksheet

Sub test()
TheSheet.Cells(1, 1) = TheSheet.Name
End Sub

Property Let TheSheet(ByVal Value As Worksheet)
Set mTheSheet = Value
End Property
Property Get TheSheet() As Worksheet
Set TheSheet = mTheSheet
End Property
--------------------------------------------------------

***I have 3 worksheets, and in each of them I have the following:

--------------------------------------------------------
Private Sub Worksheet_Activate()
Dim x As New Class1
x.TheSheet = Me
cc.Add x
End Sub
--------------------------------------------------------

***Then in a regular module I have the following:

--------------------------------------------------------
Public cc As New Collection

Sub Main()
For Each WS In Worksheets
WS.Activate
Next WS
RunMethods
End Sub

Sub RunMethods()
Dim x As Class1
For Each x In cc
x.test
Next
End Sub
--------------------------------------------------------
***
Running Main accomplishes the goals of executing the method in each of the
classes instantiated by activated each of the worksheets.

I hope this is of help.

"Nik" wrote:

Hi,

I have an class instance on all my worksheets, is there some way I
can dynamically execute a method on these objects? Something like

for each worksheet in worksheets
worksheet.myObject.myMethod
next worksheet

?

I'm not sure if this is what you want. You probably need to set up a
collection to hold the instances of your class -- this needs to be set up in
a module -- not a class module. See the following:

Aah, thanks. That was what I was looking for. Too bad events have to
be enabled for it to work so
there is some flickering but at least it works.

Okay -- I never thought about events and screenupdating before. I know it's
tangential to you initial concern, but after reading your post I tried a few
things and realize I don't understand how screenupdating is controlled --
maybe you know?

When you mentioned that you get flickers because you need events I figured
that you couldn't turn off updating and still get your events. (I may have
completely misunderstood, and you were considering turning off events.)
Anyway, I wrote a small progam to set screenupdating=false. Then put an
SelectionChange event in a worksheet to add the value of screenupdating to a
collection (didn't want to mess it up with a msgbox). This was followed by a
stop.

I looked at the collection (in the immediate pane) and saw that
screenupdating had been turned to true. I did check by add it's status to
the collection immediately after setting it (in a macro in a module).

Do you know how this works? Does an event being triggered automatically
turn it back on? Aren't you sorry you put in that gratuitous information?
<g.



"Nik" wrote:

I'm not sure if this is what you want. You probably need to set up a
collection to hold the instances of your class -- this needs to be set up in
a module -- not a class module. See the following:

Aah, thanks. That was what I was looking for. Too bad events have to
be enabled for it to work so
there is some flickering but at least it works.