?
Solved

VBA, Global SheetSelectionChange for All Workbooks

Posted on 2011-09-11
8
Medium Priority
?
1,227 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 143

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 93

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 143

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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 81

Accepted Solution

by:
byundt earned 2000 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 93

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 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