Disable Change-event macro
 

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

Disable Change-event macro
 

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

Disable Change-event macro
 

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

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

Disable Change-event macro
 

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

Disable Change-event macro
 

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

Disable Change-event macro
 

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



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