Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code & modules are gone, Macro warning message remains
Hy Byron,
Take a look at Chip Pearson's Deleting All VBA Code In A Project http://www.cpearson.com/excel/vbe.htm If he missed something, I'm sure he would be interested. But you will see that it deletes Standard Modules, Forms, Class Modules as well as emptying sheets of code. I believe you did not remove the forms. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Byron" wrote in message ... I have an Excel Workbook where users can fill out a form and then press a command button on the Sheet that saves a copy of the file into a pre-defined directory. I don't want the copied version of the Workbook to have any of the VBA code or the command button in it so I tried to remove them using the following code inside the copy subroutine: ... With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule .DeleteLines 1, .CountOfLines End With ActiveWorkbook.ActiveSheet.OLEObjects("My_Button") .Delete ... The code and the button are removed just fine from the copied file, but when I open the new copy of the Workbook, Excel gives me the Macro warning message. I have checked in the Visual Basic Editor and there are no additional modules attached to the Workbook and there is no code in any of the Sheet modules or the ThisWorkbook module. How can I completely get rid of the button and code so that I no longer get the Macro warning message? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code & modules are gone, Macro warning message remains
Hi David,
I don't have any forms in the original workbook, just two command buttons on one of the sheets and some code in the module for that sheet. When I press one of the buttons, the code runs and at the end I am left with excel open and the new copy of the workbook showing. I exit out of excel completely, and then navigate in Windows Explorer to the new file (which wasn't there before). I open the file (and therefore excel) from there and that is when I get the Macro warning. Then I dismiss the warning, save the document without making any changes, exit excel and then re-open the doucment and the warning is gone. Seems really bizarre to me, so if you have any other suggestions, let me know. Thanks. My system: Windows XP Pro (SP 1) Microsoft Office Professional Edition 2003 (SP 1) "David McRitchie" wrote: Hi Byron, (posted reply to programming) Mystery to me. Chip would have written this originally in Excel 2000. So many people use it that I can't imagine it not being good for later versions as well. Forms are certainly deleted, but it might not be a common practice to delete macros if you have forms. Are you exiting Excel, or just the workbook. If just exiting the workbook, try exiting Excel, then reopening the workbook. If that works don't ask me what difference it would make, but of course, your feedback would be needed. How are you opening the file, can you verify that you are opening the file you think you are opening by going down through the directories from "My Computer". (if a PC). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Byron" wrote in message ... Thanks for you response David, I adapted my code from some I found on Chip Pearson's site in the first place. I modified my code to use his exact DeleteAllVBA function (after your suggestion) with a small modification, and I still have the same problem. Weird thing is, if I open the copied excel file the first time, it gives me the macro warning. If I choose to accept macros and then save the file without making any other changes, the next time I open it up the warning is gone. It's like when saving it as a user includes some extra check that saving it programmatically does not. I really would like the copied file to not have the warning message, so any more suggestions would be appreciated. Thanks. Here is the code as it stands right now: Public Sub CreateCopy() ' save a copy of excel workbook ThisWorkbook.SaveAs FileName:=("C:\Temp\" + ThisWorkbook.Name) ' delete buttons from new version ActiveWorkbook.ActiveSheet.OLEObjects("GenerateDoc umentsEnglish_Button").Delete ActiveWorkbook.ActiveSheet.OLEObjects("GenerateDoc umentsFrench_Button").Delete ' remove code from new version of the pricing spreadsheet DeleteAllVBA ActiveWorkbook ' delete export worksheet Application.DisplayAlerts = False ActiveWorkbook.Unprotect ActiveWorkbook.Sheets("Export").Delete ActiveWorkbook.Protect Application.DisplayAlerts = True ' save changes ActiveWorkbook.Save End Sub ' following code adapted from http://www.cpearson.com/excel/vbe.htm Sub DeleteAllVBA(TargetBook As Excel.Workbook) Dim VBComp As VBIDE.VBComponent Dim VBComps As VBIDE.VBComponents Set VBComps = TargetBook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub "David McRitchie" wrote: Hy Byron, Take a look at Chip Pearson's Deleting All VBA Code In A Project http://www.cpearson.com/excel/vbe.htm If he missed something, I'm sure he would be interested. But you will see that it deletes Standard Modules, Forms, Class Modules as well as emptying sheets of code. I believe you did not remove the forms. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Byron" wrote in message ... I have an Excel Workbook where users can fill out a form and then press a command button on the Sheet that saves a copy of the file into a pre-defined directory. I don't want the copied version of the Workbook to have any of the VBA code or the command button in it so I tried to remove them using the following code inside the copy subroutine: ... With ActiveWorkbook.VBProject.VBComponents("Sheet1").Co deModule .DeleteLines 1, .CountOfLines End With ActiveWorkbook.ActiveSheet.OLEObjects("My_Button") .Delete ... The code and the button are removed just fine from the copied file, but when I open the new copy of the Workbook, Excel gives me the Macro warning message. I have checked in the Visual Basic Editor and there are no additional modules attached to the Workbook and there is no code in any of the Sheet modules or the ThisWorkbook module. How can I completely get rid of the button and code so that I no longer get the Macro warning message? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Warning Message | Excel Discussion (Misc queries) | |||
Macro Warning Message | Excel Discussion (Misc queries) | |||
Stoping the Macro Warning Message | Excel Discussion (Misc queries) | |||
What is the VBA code to delete a sheet without warning message? | Excel Discussion (Misc queries) | |||
getting a macro warning, even through there is no code | Excel Programming |