Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
disable combo change event temporaraly | Excel Programming | |||
how to disable listbox change event | Excel Programming | |||
Enable/Disable Worksheet Change Event code | Excel Programming | |||
Disable Change Event in Form | Excel Programming | |||
Disable Worksheet change event | Excel Programming |