Disable Change-event macro

[clr]

Hi All....
I have a change-event macro on a sheet that calls another macro from another
module.....this works fine

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$8" Then
Call FormatCurrency
Else
End If
End Sub

Problem is,
1-When I make a copy of that sheet, to save as another workbook, the
ChangeMacro goes with the new sheet and the new workbook does not have the
'FormatCurrency" macro.
2-I then run another macro to delete entire rows from well below row 8, and
the ChangeMacro triggers and I get a "Compile error: Argument not
optional"and the macro stops on the "Call FormatCurrency" line . It
should not even trigger because I am not changing cell E8.

Any help or thoughts would be apprecaited

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Add a do-nothing FormatCurrency macro, then it will compile okay.

 

[clr]

Hi Bob......
Thanks for the response, and I understand the theory, but I don't know
exactly how to go about doing that. I can't add it before I do the copy
because there is already a real FormatCurrency macro in the mother
workbook........and I don't know how to do it under program control to the
child workbook

Maybe I'll diddle with some sort of additional IF statement to use a helper
cell value to allow or dis-allow the Change event macro. I was just hopeing
there was an easy way to disable that macro.

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

Disabling it doesn't seem to be the issue Chuck, it doesn't get invoked
unless you change E8. The issue is that it is not compiling, so you need to
overcome that, that is you need to have such a procedure.

How about putting the formatcurrecncy macro into the sheet code module, so
that it goes with the sheet?

 

[clr]

Hi Bob.......
Thanks again for your time and consideration. Late, LATE last night, I
discovered a work-around for my problem. Instead of using the template of
my sheet as the working sheet, I make a copy of it in the same mother
workbook and use that copy for my working sheet, then I can delete the
undesired rows before I go in to the "copysheet" stage for my save to a
child workbook and all follows well. This allows me to delete the rows and
not affect the template, which is good.

As for your suggestion of moving the FormatCurrency macro to the sheet
module.......it worked fine when I tried it with one sheet........but
actually I have several sheets that access this macro and go through this
same process, and so will have to copy it in to all of them and do further
testing.......but it does seem like a good solution and simpler than my
work-around. Many thank-you's Bob

Vaya con Dios,
Chuck, CABGx3

 

[Bob Phillips]

You could make it a public procedure and just precede the call with the
sheets codename, like

Call Sheet1.FormatCurrency

which will allow it to be called from elsewhere as well.

 

[clr]

I'll have to look in to that some..........right now, I'm happy with what
you gave me before..

Thanks again,
Vaya con Dios,
Chuck, CABGx3