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.

--
__________________________________
HTH

Bob

"clr" wrote in message
...
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" wrote in message
...
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?

--
__________________________________
HTH

Bob

"clr" wrote in message
...
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" wrote in message
...
Add a do-nothing FormatCurrency macro, then it will compile okay.

--
__________________________________
HTH

Bob

"clr" wrote in message
...
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