Call Module Subroutine from SelectionChange Event

[Caleeco]

Hello,

I'm having a code structure issue, so the code below is just representative of the problem.

I have several sheets which I would like to have a SelectionChange Event trigger some code to run. There is one routine that I have that needs to run regardless of which SelectionChange event it triggered, so I created a module with said code in the hopes I could CALL it from each of the Sheet codes.

Worksheet Sheet1 Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call ColourCell
End Sub

Module Level Sub-Routine i want to call

Public Sub ColourCell()
    ActiveCell.Interior.Color = vbYellow
End Sub

However, upon triggering the SelectionChange event, i get a Compile Error saying that "Sub or Function not defined".

The only way around this is copying the ColourCell() routine into each of the sheet codes, which doesn't feel like the most efficient solution.

Any help is appreciated.
Cheers

Caleeco

 

[DanteAmor]

Put the following code in the events of thisworkbook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Call ColourCell
End Sub

Put the following in a module

Public Sub ColourCell()
    ActiveCell.Interior.Color = vbYellow
End Sub

It works for me. Select any cell on any page and color it

 

[Caleeco]

Hi Dante,

Thanks for that solution. I didnt know you could have event code in 'ThisWorkbook'. It does work for this example, however, the code I currently have is much more complex.

I also have IF NOT INTERSECT method to only trigger code on certain selections (the range differers for each sheet).

I tried the below, which works for selections on Sheet1. However, if i Select anything on Sheet 2. I get a Method 'Intersect' of Object '_Global' failed error

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Intersect(ActiveCell, Sheet1.Range("A1:A10")) Is Nothing Then
        MsgBox "The active cell does NOT Intersect A1:A10"
        Call ColourCell
    End If
End Sub

Thanks for your assistance
Caleeco

 

[DanteAmor]

Forget the macros a little. Better explain what you need to do on each sheet.

 

[Norie]

Caleeco

You should be using Sh and Target in the code, they are references to the sheet the selection has been changed on and the range that has been selected.