VBA, Global SheetSelectionChange for All Workbooks


I have the following code in ThisWorkbook VBA Excel Objects of my file book1.xlsm

It works ok. It displays the ActiveSheet.Name everytime I change Selection.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

MsgBox ActiveSheet.Name

End Sub

Open in new window


Is it possible to make it work in other opened workbooks as well?
Also Is it possible to display the ActiveSheet.Name ONLY if I change WorkSheet and not every time I click on a different cell..

Thanks in advance
...
LVL 1
New_AlexAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
Try putting the following code in ThisWorkbook. When you open that workbook, it will start trapping application level events--activating a worksheet within the existing workbook and activating a new window (with its own workbook and activeworksheet). By trapping these events, the code doesn't run when you change the selection of cells within a worksheet.

Private WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
MsgBox Sh.Name
End Sub

Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
MsgBox Wb.ActiveSheet.Name
End Sub

Private Sub Workbook_Open()
Set App = Application   'Instantiate application level events
End Sub

Open in new window


I like to refer to Chip Pearson's guide to application level events at http://www.cpearson.com/Excel/AppEvent.aspx

Brad
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it possible to make it work in other opened workbooks as well?
no, this is not possible. because that code is "per workbook".

>Also Is it possible to display the ActiveSheet.Name ONLY
yes, that is possible.
you have to remember the previous sheet name :
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 'declare the variable as static, so it will be remembered even through the next call of this function (event)
 static str_previous_active_sheet_name  as string
 'compare the current value with previously stored value
 if str_previous_active_sheet_name  <>  ActiveSheet.Name then
    'so, it's different. show it !
    MsgBox ActiveSheet.Name
    ' and stored it for next call, if any
    str_previous_active_sheet_name = ActiveSheet.Name 
 end if
End Sub

Open in new window

0
 
Patrick MatthewsCommented:
Actually, yes it is possible: you can enable Application-level events using class modules.

I am going out for a few hours now, so maybe another Expert will chime in :)
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
hmm. I know you can catch application-level events:
http://support.microsoft.com/kb/213566

but I didn't remember that you could catch the selection events? ...
0
 
Patrick MatthewsCommented:
Extending Brad's suggestion a bit...

If you want that code to "always" be available, then you should add it to a Personal Macro Workbook, or to an add-in file.
0
 
byundtCommented:
As a practical matter, when I was debugging the code I often found it necessary to rerun the Workbook_Open sub. Errors and code editing frequently broke the application-level event trapping.

I could have made my life easier by running a sub like this:

Sub Instantiate()
Set App = Application   'Instantiate application level events
End Sub

Open in new window


Because App is declared as a Private variable, you must put this sub in the ThisWorkbook code pane along with the previously suggested event macros. You would then be looking to run the macro ThisWorkbook.Instantiate

Brad
ApplicationEventsQ27302099.xlsm
0
 
byundtCommented:
When I changed the declaration of App from Private to Public and moved the Instantiate sub to a regular module sheet, I had to change its code to:
Sub Instantiate()
Set ThisWorkbook.App = Application   'Instantiate application level events
End Sub

Open in new window


With the above changes, the application level events continue to be trapped.

Brad
ApplicationEventsQ27302099.xlsm
0
 
New_AlexAuthor Commented:
Thank you SIR !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.