How to register an event handler when excel opens

[John Davidson]

Folks,

I have written an event handler to to validate the work book name before
saving a work book. Ineed to know what code do I have to write to have the
event handler register itself when Excel starts.

Also is there some install procedure where I can move my code and user
from into the personal.xls in the XLSStart directory? I can do it by hand
but that is a pain.

Thanks,

{John}

I miss Cusp and Mesa.

 

[Tom Ogilvy]

See Chip Pearson's page on Application Level events

http://www.cpearson.com/excel/appevent.htm

for writing code with code
http://www.cpearson.com/excel/vbe.htm

 

[keepitcool]

John,

you'll need to setup a classmodule to hook into
excel's application object.from there you can monitor all events
within the running instance of excel.

Here's how:

in your app:

insert a new classmodule
rename it to :clsAppEvent


'code for clsAppEvent
Option Explicit
Dim WithEvents xlApp As Application
Private Sub Class_Initialize()
Set xlApp = Application
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "you opened " & Wb.Name
End Sub


'code for ThisWorkbook
Option Explicit
Dim appMon As clsAppEvent
Private Sub Workbook_Open()
Set appMon = New clsAppEvent
End Sub

as long as there's no stateloss.. everytime a workbook is opened
you'll get a message.

Hopefully this is illustrative of the technique,
now you can search & find lots of other examples.




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[John Davidson]

I have code to register my class module what I need to do is cause this
code to execute when Excel starts up sort of like a autoexec.

{John}

 

[Tom Ogilvy]

Code would have to be in a workbook that is opened when Excel opens.
Choices would be

Personal.xls
an addin that will be loaded
Other workbook in the xlstart directory.

Then the workbook_Open event would have to trigger the code you have
written.

If Excel is opened programmatically via automation, non of these files would
be loaded by default.

To establish this environment, you would need the user to run some type of
install routine.