Solved

VBA, Global SheetSelectionChange for All Workbooks

Posted on 2011-09-11
8
1,178 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 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 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 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

730 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