Worksheet Selection Change in all Worksheets

S

Schofi-D

New member
Joined
Sep 14, 2017
Messages
6
Reaction score
0
Points
0
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
Click to expand...
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
 
Last edited:
Add this to the ThisWorkbook code module in Personal.xlsb. The yellow was a tad bright, so I toned it down :)

Code: 
Option Explicit
Private WithEvents app As Application
Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Static xRow As Long
Static xColumn As Long
    
    Application.ScreenUpdating = False
    Cells.Interior.ColorIndex = 0
    
    
    If xColumn <> "" Then
    
        Columns(xColumn).Interior.ColorIndex = xlNone
        Rows(xRow).Interior.ColorIndex = xlNone
    End If
    
    With Selection
    
        xRow = .Row
        xColumn = .Column
        With Sh.Columns(xColumn).Interior
        
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
        
        With Sh.Rows(xRow).Interior
        
            .ColorIndex = 6
            .Pattern = xlSolid
        End With
    End With
End Sub
 
Last edited:
Hi Bob

Thanks for this but it still doesn't work for me, my code works in the worksheet it is placed into, and i kind of understand the part you've added to make it work in any open workbook, but doesn't work for me, could this be because im using excel 2010? i cant call the macro either from the developer tab, but would assume as its in the personal workbook module that it should just run when moving cells?

Dan
 
No, it will work fine with Excel 2010. I have used this technique since XL97 I am sure. I changed your code quite a bit, mainly in style and redundancy, I left the essence of what you had.

What I have done is to add application events. This then applies to any workbook because it is at the application level. Excel doesn't have anywhere in the application to store and run VBA code, so you have to add it to a workbook, I suggested Personal.xlsb because that should open whenever you start Excel.

You need to run the workbook open routine, so either execute that with F5, or re-open the workbook.
 
Ok i added the workbook open event but then the code kept breaking at
if xColumn <> "" Then
Click to expand...
So i removed that part and just left the With Selection routine, but now when you change cells the previous column is higlighted still whereas before it cleared the format then re highlighted the column and row, So it seems that its something here
Application.ScreenUpdating = False Cells.Interior.ColorIndex = 0


If xColumn <> "" Then

Columns(xColumn).Interior.ColorIndex = xlNone
Rows(xRow).Interior.ColorIndex = xlNone
End If
Click to expand...

Every new book that is open is blank, and every saved file will have something somewhere, hence why i removed this, but now im thinking i need to clear the highlighting before running the code
 
Sorry that should have been If xColumn <> 0 as I changed the type to Long.
 
Ok so what i done was this in excel objects - workbook

Option Explicit
Private WithEvents app As Application




Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Static xRow As Long
Static xColumn As Long

Range("A1:DD60000").Interior.Color = xlNone

With Selection

xRow = .Row
xColumn = .Column
With Sh.Columns(xColumn).Interior

.ColorIndex = 40
.Pattern = xlSolid
End With

With Sh.Rows(xRow).Interior

.ColorIndex = 40
.Pattern = xlSolid
End With
End With
End Sub

Sub Workbook_Open()
Set app = Application
End Sub
Click to expand...

But then realized this is not ideal on every single workbook open and i want to call the macro from my toolbar as and when i require it, so i put the workbook_open event in the personal.xlsb module, so i can rename it and assign it to the toolbar, but that doesn't call the macro?
 
If you put a button on the toolbar (do you mean the ribbon?), you still need somewhere to store the code, so again I suggest Personal.xlsb. What I have done in the past is have code like this but I have buttons on my ribbon that turn the function on or off.
 
Yeah ribbon (quick access one) ok that would make sense, il give that a go thanks
 
Sorted, i just added a msgbox on the open event and used vbyesno, either use the rule or not, works perfectly, thanks for your help :)
 
You must log in or register to reply here.
Back
Top