New_Alex
asked on
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
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
...
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 :)
I am going out for a few hours now, so maybe another Expert will chime in :)
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? ...
http://support.microsoft.com/kb/213566
but I didn't remember that you could catch the selection events? ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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:
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
I could have made my life easier by running a sub like this:
Sub Instantiate()
Set App = Application 'Instantiate application level events
End Sub
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
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:
With the above changes, the application level events continue to be trapped.
Brad
ApplicationEventsQ27302099.xlsm
Sub Instantiate()
Set ThisWorkbook.App = Application 'Instantiate application level events
End Sub
With the above changes, the application level events continue to be trapped.
Brad
ApplicationEventsQ27302099.xlsm
ASKER
Thank you SIR !
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 :
Open in new window