Fire Worksheet_change event from module

[avi]

Hello,

Is there a way to trigger a worksheet_change event from code placed in
a regular module and not in the sheet itself?

Or what is the approach to trigger a worksheet change event when I
don't know in advance which sheet will be involved and don't want to
insert code at each created sheet?

Thanks a lot
Avi

 

[Peter T]

Change Private to Public, eg

Public Sub Worksheet_Change(ByVal Target As Range)

and to call it from elsewhere in your project, eg

Sheet1.Worksheet_Change Range("A1")
Worksheets("Sheet1").Worksheet_Change Range("A1")

Unless you are certain Sheet1 will never be deleted, call with
Worksheets("Sheet1").

Whilst this should work, I think a better way would be to move the bulk of
the event code to a normal module, eg

Sub wsChange(ws As Worksheet, Target As Range)
' code
End Sub

from the worksheet event call it like this
Private Sub Worksheet_Change(ByVal Target As Range)
wsChange Me, Target
End Sub

Call it similarly from anywhere else but change 'Me' to [say] ActiveSheet

Regards,
Peter T

 

[OssieMac]

Hi Avi,

The code goes in ThisWorkbook module.

The event is the following.

Private Sub Workbook_SheetSelectionChange _
(ByVal Sh As Object, ByVal Target As Range)

Sh is the worksheet and Target is the range.

If you only want the code to run with some worksheets then you can use
If/Then/Else or Select Case and test for the worksheet name/s.

 

[joel]

You could create a class module to handle the change event. You can se
the events in a sheet change event

See chip's webpage 'Events In Excel VBA
(http://www.cpearson.com/EXCEL/Events.aspx

 

[Peter T]

Think I misread your question. As OssieMac suggests use the events exposed
in the ThisWorkbook module, if necessary include some If or Select case to
cater for different 'set's of sheets.

Regards,
Peter T

Change Private to Public, eg

Public Sub Worksheet_Change(ByVal Target As Range)

and to call it from elsewhere in your project, eg

Sheet1.Worksheet_Change Range("A1")
Worksheets("Sheet1").Worksheet_Change Range("A1")

Unless you are certain Sheet1 will never be deleted, call with
Worksheets("Sheet1").

Whilst this should work, I think a better way would be to move the bulk of
the event code to a normal module, eg

Sub wsChange(ws As Worksheet, Target As Range)
' code
End Sub

from the worksheet event call it like this
Private Sub Worksheet_Change(ByVal Target As Range)
wsChange Me, Target
End Sub

Call it similarly from anywhere else but change 'Me' to [say] ActiveSheet

Regards,
Peter T

Hello,

Is there a way to trigger a worksheet_change event from code placed in
a regular module and not in the sheet itself?

Or what is the approach to trigger a worksheet change event when I
don't know in advance which sheet will be involved and don't want to
insert code at each created sheet?

Thanks a lot
Avi