Make SheetSelectionChange operate on a different workbook

[stevenjwilson]

I use Workbook_SheetSelectionChange in the "ThisWorkbook" object of a excel file, file 1. I have Module code in this file that I make available as commands on the Excel ribbon.

The problem I have is that I want the SheetSelectionChange event to monitor a different workbook, file 2, but of course it doesn't .... its only active on file 1. The scenario is this: file 1 is closed; I open file 2, the commands are on the Excel ribbon, I can run those OK and they operation on the current workbook that is open, but of course I get no Workbook_SheetSelectionChange functionality because file 2 has no macros in it.

I have been trying to figure out options:
1) I can add the macros to file 2, but I dont want to do this. I want them to be macro free.
2) I have been reading more about how "events" work to see if there is a way to make an event monitor in file 1, that can monitor events in another workbook. I have been reading about Class Modules and wonder if that is an answer.....

In Pseudo code: instead of
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
I want
Private Sub Workbook_SheetSelectionChange(wb As Object, ByVal Sh As Object, ByVal Target As Range)
so I can tell it what workbook to monitor.

Any tips would be appreciated....Thanks

 

[Domenic]

You'll need to use a class module to enable application level events...

1) Create a class module (Atl+F11 > Insert > Class Module).

2) In the Properties window (F4) under Name, set the name to clsApp.

3) In the code module for the class...

Option Explicit

Public WithEvents XL As Application

Private Sub XL_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox Sh.Parent.Name & ", " & Sh.Name & ", " & Target.Address
End Sub

4) In the code module for ThisWorkbook...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Dim[/COLOR] xlApp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]New[/COLOR] clsApp

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_BeforeClose(Cancel [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR])
    [COLOR=darkblue]Set[/COLOR] xlApp.XL = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_Open()
    [COLOR=darkblue]Set[/COLOR] xlApp.XL = Application
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

5) Close and re-open the workbook.

Note that other event handlers will also be available in the class module.

Hope this helps!