Worksheet Selection Change to work on all sheets

[Schofi_D]

Hi

I have a macro that works fine if it is in the workbook that is being used, but i want to be able to make it work in any active workbook by assigning the macro to the quick access toolbar, but i cant get a worksheet selection change event in a module in my personal xlsb.

This is my code i am running in the individual workbooks code, but how do i make this work in any sheet under my personal xlsb?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Static xRow
Static xColumn
If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

Can you create a selection change macro in all workbooks? once the code has been activated/run from the toolbar with the macro associated to the button

Thanks

Dan

 

[RickXL]

Hi, welcome to the MrExcel Message Board.

As standars you can use worksheet events and workbook events. With a bit of code you can also use Application level events.

That means that you can act on certain actions which are raised in that instance of Excel. To do it you need to create a new class with some code in it. The easy way to utilise that Class is to use it in the ThisWorkbook module of your Personal.xlsb.

I have not replicated your code but the basic system is like this. My code just displays a Msgbox when the selection changes. Just remove that and put your code in there.

Create a new Class module and rename it to c_App. (That name is used in the code so needs to match.)
This code needs to go into the Class module:

Public WithEvents App As Application
  
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "Selection Change " & Sh.Parent.Name & " " & Sh.Name & " " & Target.Address
End Sub

This code needs to go into the ThisWorkbook module of your Personal.xlsb.

Dim newApp As New c_App
 
Private Sub Workbook_Open()
    Set newApp.App = Application
End Sub

Now you need to close Excel and restart it. After that, when you change selection a Msgbox will appear that displays the Workbook name, the Sheet name and the selected range address.
To adapt it to your needs, take out the red line and insert your own code.

If you need more background information about how it all works, start here: http://www.cpearson.com/excel/appevent.aspx


Regards,