How to Control Worksheet Event Handler in Module?

tinfanide · #1 10-17-2014, 12:39 AM

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

For example, the above codes must be put under a sheet to be effective. Is it possible to make it effective under a module? Because I want to use the codes contained in a module every time for any other Excel files.

Any help is appreciated.

macropod · #2 10-17-2014, 09:51 PM

As you've already observed, the Worksheet_SelectionChange code has to be run at the sheet level. Depending on your objective, you may be able to use the Workbook_SheetChange event, which runs from the 'ThisWorkbook' module and applies to all sheets.

CoolBlue · #3 10-19-2014, 09:46 AM

You could create a class Module to wrap the worksheet.
By way of a very basic example...

In a Class Module called clWsEvents

Code:

Dim WithEvents Ws as Worksheet

Private Sub Ws_SelectionChange(ByVal Target As Range)
    'Do stuff
End Sub

In a Standard Module

Code:

Dim gWs as clWsEvents

Sub Main()
    Set gWs = New clWsEvents
     Set gWs.Ws = ActiveWorkbook.Sheets('sheetName')
    'Do other stuff
End Sub

There are many possible refinements and I may not be letter-perfect with the syntax, but this is the basic idea. Feel free to google Vba Class Modules and WithEvents to inform yourself about it.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Inserting a Word Module into excel	SarahBear	Excel Programming	12	06-11-2014 05:26 AM
How to summarise different worksheet to a summary worksheet	samkiewhock	Excel	1	09-06-2012 03:34 AM
AZWizard Module - ?hidden module	pcaldwell	Word	1	08-22-2012 01:19 PM
"Auto-populating" data-worksheet to worksheet.	meggenm	Excel	4	02-04-2012 02:04 AM
Outlook 2002 crashing - faulting module msmapi32.dll	Ken Cuvelier	Outlook	3	12-12-2005 03:55 PM