Using the Event Handler

Posted by brian charles on May 05, 2000 10:45 AM

As i have never used the event handler before, I am
having difficulties. I found a reference on the web,
that led me to create the following code, but it does
not run. I would like to run the macro "SpadeChartSetup"
upon activating the sheet named "Chart".

ANy help is greatly appreciated.

CODE:

Public WithEvents appevent As Application

Private Sub appevent_SheetActivate(ByVal Sh As Object)

If ActiveSheet.Name = Chart Then
Application.Run TMacSpadever.xls!SpadeChartSetup
End If

End Sub

Posted by Ivan Moala on May 05, 2000 1:57 PM

Brian
You need to enclose the TMacSpadever.xls!SpadeChartSetup with commas eg
"TMacSpadever.xls!SpadeChartSetup"

The other thing I noticed is that you are actually using or defining this event as a new
Class. If you are using excel 97 the event is
already present as;
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Ivan

Posted by brian charles on May 08, 2000 10:57 AM

Thanks Ivan, once again you have helped me out..
But... Now the macro's run fine IFF I manually
select the sheet named "Chart" but do not run when
the sheet is selected upon clicking a hyperlink.

ANy suggestions.....

Posted by Ivan Moala on May 08, 2000 1:09 PM

Brian
Unfortunately selecting a sheet via a hyperlink
does not trigger an event so the sheet activate
does not fire.
Lets see if there is a way around it.....?

Ivan