Results 1 to 10 of 10

Thread: Worksheet Selection Change in all Worksheets

  1. #1

    Worksheet Selection Change in all Worksheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Last edited by Schofi-D; 2017-09-14 at 09:21 AM. Reason: Quote marks

  2. #2
    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 by Bob Phillips; 2017-09-14 at 10:27 AM.

  3. #3
    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

  4. #4
    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.

  5. #5
    Ok i added the workbook open event but then the code kept breaking at
    if xColumn <> "" Then
    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
    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

  6. #6
    Sorry that should have been If xColumn <> 0 as I changed the type to Long.

  7. #7
    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("A1D60000").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
    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?

  8. #8
    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.

  9. #9
    Yeah ribbon (quick access one) ok that would make sense, il give that a go thanks

  10. #10
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •