How to call a workbook Event..

[Matt]

Hi,
I would like to know how to initiate a workbook event from a
subroutine. It seems like it should be something like Call
Workbook_Open
Thanks,
Matt

 

[Gary''s Student]

There are two general approaches:

1. Use the Context
Say the workbook code is:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox ("sheet added")
End Sub

and we want this executed by another macro. Just make the macro add a sheet:

Sub newsheet()
Sheets.Add
End Sub

and the Event gets triggered.


2. Without Context

Here we want the code executed, but without actually adding a sheet. Here
make the workbook code:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call sheetsettup
End Sub

where sheetsettup is a macro in a standard module. Now any other macro can
also call sheetsettup.

 

[Bob Phillips]

Almost. It would be

Call ThisWorkbook.Workbook_Open

but you must make Workbook_Open a Public procedure, the default signature is
private.

--
HTH

Bob

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