WorkSheet_Change for Multiple Sheets

[mgw1138]

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

 

[NoSparks]

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]

Alternatively use a workbook event, rather than a worksheet event.

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]

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