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
--
HTH...

Jim Thomlinson


"Horatio J. Bilge, Jr." wrote:

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

You may want to see if you could use the Workbook_SheetChange event.



Horatio J. Bilge, Jr. wrote:

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

--

Dave Peterson

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

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Horatio J. Bilge, Jr." wrote:

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

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

Parent is the sheet that the range came from.
--
HTH...

Jim Thomlinson


"Horatio J. Bilge, Jr." wrote:

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

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Horatio J. Bilge, Jr." wrote:

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

Thanks for the tip. I made that change, and it is working well.
I appreciate the help.
~ Horatio

"Jim Thomlinson" wrote:

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

Parent is the sheet that the range came from.
--
HTH...

Jim Thomlinson


"Horatio J. Bilge, Jr." wrote:

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

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Horatio J. Bilge, Jr." wrote:

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

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

"Dave Peterson" wrote:

You may want to see if you could use the Workbook_SheetChange event.



Horatio J. Bilge, Jr. wrote:

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

--

Dave Peterson

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.



Horatio J. Bilge, Jr. wrote:

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

"Dave Peterson" wrote:

You may want to see if you could use the Workbook_SheetChange event.



Horatio J. Bilge, Jr. wrote:

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

--

Dave Peterson


--

Dave Peterson