View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips[_3_] Bob Phillips[_3_] is offline
external usenet poster
 
Posts: 2,420
Default Disable Change-event macro

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