WorkSheet_Change for Multiple Sheets

mgw1138

New Member
Joined
Mar 9, 2014
Messages
28
I have an extensive Worksheet_Change subroutine that works perfectly on Sheet6 where it is located. The routine is written so that everything accesses "activesheet" rather than the sheet name. I have parallel structures on Sheet3 and Sheet9 but they are not accessing the subroutine. I tried this:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet6.Worksheet_Change (Target)
End Sub

And get an undefined object error. Can someone explain the rules for accessing subroutines and functions in another sheet.

Thanks so Much
Michael
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
888
Office Version
2010
Platform
Windows
Put your code into a sub in a standard module and have the Worksheet_Change event of each desired sheet call that sub rather than the Change sub of another sheet.

As I understand it, Private subs can only be called from the module they are in.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,524
Office Version
365
Platform
Windows
Alternatively use a workbook event, rather than a worksheet event.
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Right(Sh.CodeName, 1) Like "[3,6,9]" Then
    MsgBox Sh.Name
    End If
End Sub
The message box will appear is you change sheet3, sheet6 or sheet9
 

mgw1138

New Member
Joined
Mar 9, 2014
Messages
28
Put your code into a sub in a standard module and have the Worksheet_Change event of each desired sheet call that sub rather than the Change sub of another sheet.

As I understand it, Private subs can only be called from the module they are in.

Works perfect - thanks - mental note to put all subs (other than event handler) in a Module
 

Watch MrExcel Video

Forum statistics

Threads
1,098,858
Messages
5,465,106
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top