How to Control Worksheet Event Handler in Module?

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