Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
programatically deleted code still triggers macro warning
I am using code posted by Chip Pearson to programatically delete a
button and its associated code. The file from which the button and code are deleted is then saved and automatically inserted into an e- mail. When the attached file is opened directly from the e-mail (which the receiving customer will necessarily do) the macro warning still appears. The button code appears to be gone and when I try to clean the project with Rob Bovey's slick code cleaner, the file from which I have deleted the code does not show up as an option, which I believe indicates that there is no code. Also, if I save the file and reoopen it, I do not get the macro warning on the second opening. The code I am using is to delete the code is: Set WS = Workbooks(nWB).Worksheets("Quote") With WS .OLEObjects(CommandButtonName).Delete Set CodeMod = Workbooks(nWB).VBProject.VBComponents(.CodeName).C odeModule End With With CodeMod .DeleteLines StartLine:=1, Count:=.CountOfLines End With When the whole routing is completed the button is gone and the code no longer appears; but, I get the macro warning. Is there something else I need to add to make it understand that once this is accomplished there is no more code and therefore no need to prompt the user with the macro warning? Thanks Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
programatically deleted code still triggers macro warning
You need to delete the module as well.
Dim vbProj As Object Dim vbMod As Object Set vbProj = ActiveWorkbook.VBProject Set vbMod = vbProj.VBComponents("Module3") vbProj.VBComponents.Remove vbMod -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message oups.com... I am using code posted by Chip Pearson to programatically delete a button and its associated code. The file from which the button and code are deleted is then saved and automatically inserted into an e- mail. When the attached file is opened directly from the e-mail (which the receiving customer will necessarily do) the macro warning still appears. The button code appears to be gone and when I try to clean the project with Rob Bovey's slick code cleaner, the file from which I have deleted the code does not show up as an option, which I believe indicates that there is no code. Also, if I save the file and reoopen it, I do not get the macro warning on the second opening. The code I am using is to delete the code is: Set WS = Workbooks(nWB).Worksheets("Quote") With WS .OLEObjects(CommandButtonName).Delete Set CodeMod = Workbooks(nWB).VBProject.VBComponents(.CodeName).C odeModule End With With CodeMod .DeleteLines StartLine:=1, Count:=.CountOfLines End With When the whole routing is completed the button is gone and the code no longer appears; but, I get the macro warning. Is there something else I need to add to make it understand that once this is accomplished there is no more code and therefore no need to prompt the user with the macro warning? Thanks Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
programatically deleted code still triggers macro warning
Bob
The code is attached to a commandbutton from the control toolbox on a worksheet, so the code is actually in the Sheet2 Object, so I don't think I can delete the module. Also, opening and closing the file manually once takes care of the problem without deleting anything further. I have closed and reopened the file via VBA, but, the first time the file is opened using file-open keyboard commands, the macro warning is displayed. Since the purpose of the button is to automate some file sanitization efforts prior to e-mailing to a customer, I would like the opening of the file, as attached to the e-mail, not to trigger the macro warning. I guess I could switch to a button from the forms toolbar, so the code will be in a regular module, and then delete the module, like you pointed out. Thanks for your time on this posting as well as your many many other informative contributions. Ken On Oct 22, 1:04 pm, "Bob Phillips" wrote: You need to delete the module as well. Dim vbProj As Object Dim vbMod As Object Set vbProj = ActiveWorkbook.VBProject Set vbMod = vbProj.VBComponents("Module3") vbProj.VBComponents.Remove vbMod -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message oups.com... I am using code posted by Chip Pearson to programatically delete a button and its associated code. The file from which the button and code are deleted is then saved and automatically inserted into an e- mail. When the attached file is opened directly from the e-mail (which the receiving customer will necessarily do) the macro warning still appears. The button code appears to be gone and when I try to clean the project with Rob Bovey's slick code cleaner, the file from which I have deleted the code does not show up as an option, which I believe indicates that there is no code. Also, if I save the file and reoopen it, I do not get the macro warning on the second opening. The code I am using is to delete the code is: Set WS = Workbooks(nWB).Worksheets("Quote") With WS .OLEObjects(CommandButtonName).Delete Set CodeMod = Workbooks(nWB).VBProject.VBComponents(.CodeName).C odeModule End With With CodeMod .DeleteLines StartLine:=1, Count:=.CountOfLines End With When the whole routing is completed the button is gone and the code no longer appears; but, I get the macro warning. Is there something else I need to add to make it understand that once this is accomplished there is no more code and therefore no need to prompt the user with the macro warning? Thanks Ken- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
programatically deleted code still triggers macro warning
Upon further review, a forms toolbar button has the added benefit of
not having the code in the worksheet object, so it does not get copied to the new workbook when the sheet is copied; so I don't need to delete any code or any modules. I like that. I will need to use my new ability to delete code and modules somewhere else. On Oct 22, 3:45 pm, Ken wrote: Bob The code is attached to a commandbutton from the control toolbox on a worksheet, so the code is actually in the Sheet2 Object, so I don't think I can delete the module. Also, opening and closing the file manually once takes care of the problem without deleting anything further. I have closed and reopened the file via VBA, but, the first time the file is opened using file-open keyboard commands, the macro warning is displayed. Since the purpose of the button is to automate some file sanitization efforts prior to e-mailing to a customer, I would like the opening of the file, as attached to the e-mail, not to trigger the macro warning. I guess I could switch to a button from the forms toolbar, so the code will be in a regular module, and then delete the module, like you pointed out. Thanks for your time on this posting as well as your many many other informative contributions. Ken On Oct 22, 1:04 pm, "Bob Phillips" wrote: You need to delete the module as well. Dim vbProj As Object Dim vbMod As Object Set vbProj = ActiveWorkbook.VBProject Set vbMod = vbProj.VBComponents("Module3") vbProj.VBComponents.Remove vbMod -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message roups.com... I am using code posted by Chip Pearson to programatically delete a button and its associated code. The file from which the button and code are deleted is then saved and automatically inserted into an e- mail. When the attached file is opened directly from the e-mail (which the receiving customer will necessarily do) the macro warning still appears. The button code appears to be gone and when I try to clean the project with Rob Bovey's slick code cleaner, the file from which I have deleted the code does not show up as an option, which I believe indicates that there is no code. Also, if I save the file and reoopen it, I do not get the macro warning on the second opening. The code I am using is to delete the code is: Set WS = Workbooks(nWB).Worksheets("Quote") With WS .OLEObjects(CommandButtonName).Delete Set CodeMod = Workbooks(nWB).VBProject.VBComponents(.CodeName).C odeModule End With With CodeMod .DeleteLines StartLine:=1, Count:=.CountOfLines End With When the whole routing is completed the button is gone and the code no longer appears; but, I get the macro warning. Is there something else I need to add to make it understand that once this is accomplished there is no more code and therefore no need to prompt the user with the macro warning? Thanks Ken- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
programatically deleted code still triggers macro warning
Well I am glad I helped ( meaning I watched on as you solved it <vbg)
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message oups.com... Upon further review, a forms toolbar button has the added benefit of not having the code in the worksheet object, so it does not get copied to the new workbook when the sheet is copied; so I don't need to delete any code or any modules. I like that. I will need to use my new ability to delete code and modules somewhere else. On Oct 22, 3:45 pm, Ken wrote: Bob The code is attached to a commandbutton from the control toolbox on a worksheet, so the code is actually in the Sheet2 Object, so I don't think I can delete the module. Also, opening and closing the file manually once takes care of the problem without deleting anything further. I have closed and reopened the file via VBA, but, the first time the file is opened using file-open keyboard commands, the macro warning is displayed. Since the purpose of the button is to automate some file sanitization efforts prior to e-mailing to a customer, I would like the opening of the file, as attached to the e-mail, not to trigger the macro warning. I guess I could switch to a button from the forms toolbar, so the code will be in a regular module, and then delete the module, like you pointed out. Thanks for your time on this posting as well as your many many other informative contributions. Ken On Oct 22, 1:04 pm, "Bob Phillips" wrote: You need to delete the module as well. Dim vbProj As Object Dim vbMod As Object Set vbProj = ActiveWorkbook.VBProject Set vbMod = vbProj.VBComponents("Module3") vbProj.VBComponents.Remove vbMod -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message roups.com... I am using code posted by Chip Pearson to programatically delete a button and its associated code. The file from which the button and code are deleted is then saved and automatically inserted into an e- mail. When the attached file is opened directly from the e-mail (which the receiving customer will necessarily do) the macro warning still appears. The button code appears to be gone and when I try to clean the project with Rob Bovey's slick code cleaner, the file from which I have deleted the code does not show up as an option, which I believe indicates that there is no code. Also, if I save the file and reoopen it, I do not get the macro warning on the second opening. The code I am using is to delete the code is: Set WS = Workbooks(nWB).Worksheets("Quote") With WS .OLEObjects(CommandButtonName).Delete Set CodeMod = Workbooks(nWB).VBProject.VBComponents(.CodeName).C odeModule End With With CodeMod .DeleteLines StartLine:=1, Count:=.CountOfLines End With When the whole routing is completed the button is gone and the code no longer appears; but, I get the macro warning. Is there something else I need to add to make it understand that once this is accomplished there is no more code and therefore no need to prompt the user with the macro warning? Thanks Ken- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveX warning after control is deleted | Excel Programming | |||
Code & modules are gone, Macro warning message remains | Excel Programming | |||
Deleting macro code programatically | Excel Programming | |||
getting a macro warning, even through there is no code | Excel Programming | |||
code for lookup,& triggers. | Excel Programming |