WorkSheet_Change for Multiple Sheets

mgw1138

New Member
Joined
Mar 9, 2014
Messages
33
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top