View Single Post
 
Old 10-19-2014, 09:46 AM
CoolBlue's Avatar
CoolBlue CoolBlue is offline Windows 7 64bit Office 2013
Advanced Beginner
 
Join Date: Jun 2014
Location: Australia
Posts: 40
CoolBlue is on a distinguished road
Default How to Control Worksheet Event Handler in Module?

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.
Reply With Quote