addin detect worksheet activation

[Bill Grigg]

All,

How can an addin detect that the user has clicked on a different sheet? Do I
have to somehow put code into the ActiveWorkbook?

Bill

 

[Bob Phillips]

Option Explicit

Private WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
'your code here
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[Chip Pearson]

I'm assuming you're speaking of an XLA add-in, not a COM or VSTO
add-in.

In the ThisWorkbook module of your XLA project, put in the lines

Private WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

This creates an object named App of type Application that will trigger
all of the events of the application. Every Workbook and Worksheet
event has an Application level counterpart, so every event can be
trapped at the application level. The event that occurs when a user
moves from one sheet to another is the SheetActivate event, so use
code like the following. The Sh parameter is a Sheet object that
references the sheet to which the user just moved.

Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox "You just moved to:" & vbCrLf & _
"Workbook: " & Sh.Parent.Name & vbCrLf & _
"Worksheet: " & Sh.Name

End Sub


See www.cpearson.com/Excel/AppEvent.aspx for more information about
working with Application events.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)