Call Module Subroutine from SelectionChange Event

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
956
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
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call ColourCell
End Sub


Module Level Sub-Routine i want to call
Code:
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Put the following code in the events of thisworkbook

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

Put the following in a module

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

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

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
956
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

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Forget the macros a little. Better explain what you need to do on each sheet.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,834
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,485
Messages
5,529,143
Members
409,851
Latest member
Ingar
Top