Execute class method on all worksheets?
 

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

?

Execute class method on all worksheets?
 

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

?

Execute class method on all worksheets?
 

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

?

Execute class method on all worksheets?
 

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.

Execute class method on all worksheets?
 

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.