Can Worksheet_Change run from separate module?

  • Thread starter Thread starter Horatio J. Bilge, Jr.
  • Start date Start date
H

Horatio J. Bilge, Jr.

I have a worksheet ("MySheet") with about 300 lines of code in the
Worksheet_Change event. When a user opens the workbook, MySheet will be
copied as many times as the user indicates (maybe up to 30-40 times). As a
result, my 300 lines of code goes up to about 12,000 lines.

I am wondering if it is possible to put the code into a separate module, and
then use the Worksheet_Change event to call the code from that other module.

~ Horatio
 
Yes. You may need to modify your code depending on what it does and how it
does it...

'In the sheet
Private Sub Worksheet_Change(ByVal Target As Range)
Call Module1.DoStuff(Target)
End Sub

'In Module1
Public Sub DoStuff(ByVal Target As Range)
MsgBox Target.Parent.Name
End Sub
 
Thanks! That seems to work great. The only change I had to make in the code
was to change the Me object to ActiveSheet.

~ Horatio
 
A better option generally speaking is to change Me to
Target.parent

Parent is the sheet that the range came from.
 
Thanks for the tip. I made that change, and it is working well.
I appreciate the help.
~ Horatio
 
Jim's suggestion is working well, but if I were to use the
Workbook_SheetChange event, would I just test the sheet name first, and then
the target?
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "MySheet" Then
Exit Sub
End If
' then something like...
If Application.Intersect(Target, Range("MyRange")) Is Nothing Then
' rest of code here
End Sub

Are there benefits to using one method over the other?

~ Horatio
 
Yep.

But I would want the test to ignore upper/lower case:

if lcase(sh.name) <> lcase("mysheet") then

And you'll want to qualify that range:

if intersect(target, sh.range("myRange")) is nothing then

====
As for benefits...

If the code is almost the same in 20 worksheets (but different in 2), then using
the workbook_sheetchange with those checks seems much more efficient (for the
developer).

If the code isn't the same--or the sheet has to be copied to a different
workbook--with the code intact, then using the worksheet_change event seems
better.
 
Back
Top