Solved

VBA, Global SheetSelectionChange for All Workbooks

Posted on 2011-09-11
8
1,151 Views
Last Modified: 2012-05-12

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
...
0
Comment
Question by:New_Alex
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36518815
>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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36518837
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36518915
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 36519203
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36519341
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
 
LVL 81

Expert Comment

by:byundt
ID: 36519367
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
 
LVL 81

Expert Comment

by:byundt
ID: 36519379
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
 
LVL 1

Author Closing Comment

by:New_Alex
ID: 36545454
Thank you SIR !
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question