In a previous post (he http://tinyurl.com/26ucf3), Doug Glancy
helped me tremendously. I want to run a series of macros on every new
and opened workbook. Doug gave me instructions on how to set up a
Class module and globally capture the New and Open events.

In the Class module, I have

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
MsgBox "Somewhere, a workbook has opened"
End Sub

so "wb" is the object I want to deal with. That much I have figured
out!

What I don't know is how to use "wb" in another macro to do things to
the workbook. I could just put a whole macro code right there in the
class module. But for some reason I keep thinking that's not how I
should do it. I could be very wrong, but I thought I ought to ask
first. Any help is greatly appreciated.

Ed

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub

Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub

--
Tim Williams
Palo Alto, CA


"Ed" wrote in message oups.com...
In a previous post (he http://tinyurl.com/26ucf3), Doug Glancy
helped me tremendously. I want to run a series of macros on every new
and opened workbook. Doug gave me instructions on how to set up a
Class module and globally capture the New and Open events.

In the Class module, I have

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
MsgBox "Somewhere, a workbook has opened"
End Sub

so "wb" is the object I want to deal with. That much I have figured
out!

What I don't know is how to use "wb" in another macro to do things to
the workbook. I could just put a whole macro code right there in the
class module. But for some reason I keep thinking that's not how I
should do it. I could be very wrong, but I thought I ought to ask
first. Any help is greatly appreciated.

Ed

Hi, Tim. Thanks for the reply.

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub

So I _do_ put the pointer to another sub in the Class module.

Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub

And this macro is in a regular module elsewhere?

Ed


On Mar 28, 5:37 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub

Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub

--
Tim Williams
Palo Alto, CA



"Ed" wrote in ooglegroups.com...
In a previous post (hehttp://tinyurl.com/26ucf3), Doug Glancy
helped me tremendously. I want to run a series of macros on every new
and opened workbook. Doug gave me instructions on how to set up a
Class module and globally capture the New and Open events.

In the Class module, I have

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
MsgBox "Somewhere, a workbook has opened"
End Sub

so "wb" is the object I want to deal with. That much I have figured
out!

What I don't know is how to use "wb" in another macro to do things to
the workbook. I could just put a whole macro code right there in the
class module. But for some reason I keep thinking that's not how I
should do it. I could be very wrong, but I thought I ought to ask
first. Any help is greatly appreciated.

Ed- Hide quoted text -

- Show quoted text -

If the code is specific to your class then it makes sense to leave it in the
class module.
Extracting the workbook-processing from the event handler is beneficial if
you want to call it from another event handler or if you want the user of
the class to be able to call it directly, passing in a workbook reference.

Tim


"Ed" wrote in message
oups.com...
Hi, Tim. Thanks for the reply.

Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub

So I _do_ put the pointer to another sub in the Class module.

Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub

And this macro is in a regular module elsewhere?

Ed


On Mar 28, 5:37 pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
'MsgBox "Somewhere, a workbook has opened"
ProcessWorkbook wb
End Sub

Sub ProcessWorkbook(wb as workbook)
'do stuff with wb
with wb.worksheets(1)
.Range("A1").value="Processed!"
end with
End Sub

--
Tim Williams
Palo Alto, CA



"Ed" wrote in
ooglegroups.com...
In a previous post (hehttp://tinyurl.com/26ucf3), Doug Glancy
helped me tremendously. I want to run a series of macros on every new
and opened workbook. Doug gave me instructions on how to set up a
Class module and globally capture the New and Open events.

In the Class module, I have

Public WithEvents App As Application
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
MsgBox "Somewhere, a workbook has opened"
End Sub

so "wb" is the object I want to deal with. That much I have figured
out!

What I don't know is how to use "wb" in another macro to do things to
the workbook. I could just put a whole macro code right there in the
class module. But for some reason I keep thinking that's not how I
should do it. I could be very wrong, but I thought I ought to ask
first. Any help is greatly appreciated.

Ed- Hide quoted text -

- Show quoted text -