CODEWITHSUNDEEP
excelvbaevents
Rafał Kowalski
Rafał Kowalski

Reputation: 187

Worksheet SelectionChange event from a module

I am looking to use worksheet SelectionChange on multiple sheets.

Here is code I have to be run using the event:

Private Sub Worksheet_SelectionChange(ByVal cell_sel As Range)

Cells.Interior.ColorIndex = xlColorIndexNone
cell_sel.Interior.ColorIndex = xlColorIndexNone
cell_sel.Offset(1, 0).Interior.ColorIndex = 40
cell_sel.Offset(-1, 0).Interior.ColorIndex = 40
cell_sel.Offset(0, 1).Interior.ColorIndex = 40
cell_sel.Offset(0, -1).Interior.ColorIndex = 40

It works in a worksheet (obviously).

I would like to use this code, or any other, on ANY of the sheets as long as specific "macro toggle" is toggled/run.

There is article after article saying it is not possible, but I have been thinking about a potential solution:

Creating a macro stored in a module, which would write the procedure above into active worksheet when run/toggled, and clear the worksheet when run/toggled again.

Is it even possible with VBA and Excel?

Upvotes: 2

Views: 2159

Answers (1)

Naresh
Naresh

Reputation: 3034

Just to give a start. This may not answer this question completely.

You can run toggle macro with a button and make the test macro active or inactive in Module

Public cell_sel_Chng As Range
Public run_Test As Boolean

Sub Set_test_Toggle()
Dim ws As Worksheet
If run_Test = False Then
    run_Test = True
    Else
    run_Test = False
        For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Interior.ColorIndex = xlColorIndexNone
        Next
End If
End Sub


Sub test()

'Cells.Interior.ColorIndex = xlColorIndexNone
cell_sel_Chng.Interior.ColorIndex = xlColorIndexNone
cell_sel_Chng.Offset(1, 0).Interior.ColorIndex = 40
cell_sel_Chng.Offset(-1, 0).Interior.ColorIndex = 40
cell_sel_Chng.Offset(0, 1).Interior.ColorIndex = 40
cell_sel_Chng.Offset(0, -1).Interior.ColorIndex = 40

End Sub

in Workbook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Set cell_sel_Chng = Target
If run_Test = True Then test

End Sub

Here is an example of toggle button to activate/stop other macro. "Play/Pause" and "CrossHair Cursor" buttons are toggle macro buttons

Upvotes: 1

Related Questions