Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove All Macros
Hi All,
I have an excel spreadsheet that runs a macro on opening and then within the workbook there are various buttons running further macros (i.e. to delete rows etc). After pressing the final button, i want all evidence of macros to be removed from the sheet. When the sheet is saved, closed and then reopened, i dont want the macro enable/disable to appear. Is this possible?? I have found VBA code to remove all macro code, and when looking in Tools\macros, it appears they have all gone, but after saving, closing and re-opening, i still get the enable/disable popup, suggesting i still have macros within the sheet. Any quick responses will be greatly appreciated, on a tight deadline!! Many thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove All Macros
Try this:
Do a save as and save the file to the windows temporary folder. Use a password when you save it so that the user can not re-open it from the temporary location. Then, copy the sheets to a new workbook and save this new workbook back over the old file. This removes all the modules which can trigger a macro warning. You should also delete all macro buttons in the new workbook. Bob Flanagan Macro Systems 144 Dewberry Drive Hockessin, Delaware, U.S. 19707 Phone: 302-234-9857, cell 302-584-1771 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Steve" wrote in message ups.com... Hi All, I have an excel spreadsheet that runs a macro on opening and then within the workbook there are various buttons running further macros (i.e. to delete rows etc). After pressing the final button, i want all evidence of macros to be removed from the sheet. When the sheet is saved, closed and then reopened, i dont want the macro enable/disable to appear. Is this possible?? I have found VBA code to remove all macro code, and when looking in Tools\macros, it appears they have all gone, but after saving, closing and re-opening, i still get the enable/disable popup, suggesting i still have macros within the sheet. Any quick responses will be greatly appreciated, on a tight deadline!! Many thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove All Macros
Many thanks for your reply Bob,
The problem with this method is that the sheet is to be re-used. I.e. they open the same sheet everyday, press the first button that re-names the sheet and saves it as the new name, therefore the original sheet is unchanged and just reopened the next day to run the same process. The user will not want to have to run the below process each day. They are very short on time and want easy solutions, very frustrating for me to try and accomodate this!! Is there any way that i can remove all modules with a button just before then do the final save? Then i can get them to simply use the File\Save to save the final copy of the sheet, without any evidence of macros? Hope that explains, tearing my hair out to find the solution!! Bob Flanagan wrote: Try this: Do a save as and save the file to the windows temporary folder. Use a password when you save it so that the user can not re-open it from the temporary location. Then, copy the sheets to a new workbook and save this new workbook back over the old file. This removes all the modules which can trigger a macro warning. You should also delete all macro buttons in the new workbook. Bob Flanagan Macro Systems 144 Dewberry Drive Hockessin, Delaware, U.S. 19707 Phone: 302-234-9857, cell 302-584-1771 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Steve" wrote in message ups.com... Hi All, I have an excel spreadsheet that runs a macro on opening and then within the workbook there are various buttons running further macros (i.e. to delete rows etc). After pressing the final button, i want all evidence of macros to be removed from the sheet. When the sheet is saved, closed and then reopened, i dont want the macro enable/disable to appear. Is this possible?? I have found VBA code to remove all macro code, and when looking in Tools\macros, it appears they have all gone, but after saving, closing and re-opening, i still get the enable/disable popup, suggesting i still have macros within the sheet. Any quick responses will be greatly appreciated, on a tight deadline!! Many thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove All Macros
The code i found was the one as follows, im a bit of a VBA novice as you probably guessed! Sub removeAllCode() Dim awi 'activeWorkbookItem(index) Dim awcl As Integer 'activeWorkbook Component CountOfLines Dim count As Integer 'how many potential code modules Dim i As Integer 'loop counter On Error Resume Next count = ActiveWorkbook.VBProject.VBComponents.count For i = 1 To count Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i) awcl = awi.CodeModule.CountOfLines awi.CodeModule.DeleteLines 1, awcl Next i Set awi = Nothing ' Release the object End Sub Tom Ogilvy wrote: does the code to remove the macros also remove the modules? If not, you will get that prompt. (for general modules). for sheet modules you will need to make sure they are completely empty - no spaces left or anything like that. -- Regards, Tom Ogilvy "Steve" wrote: Hi All, I have an excel spreadsheet that runs a macro on opening and then within the workbook there are various buttons running further macros (i.e. to delete rows etc). After pressing the final button, i want all evidence of macros to be removed from the sheet. When the sheet is saved, closed and then reopened, i dont want the macro enable/disable to appear. Is this possible?? I have found VBA code to remove all macro code, and when looking in Tools\macros, it appears they have all gone, but after saving, closing and re-opening, i still get the enable/disable popup, suggesting i still have macros within the sheet. Any quick responses will be greatly appreciated, on a tight deadline!! Many thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove All Macros
No, that doesn't remove the modules. This link should get you going.
http://www.cpearson.com/excel/vbe.htm -- Regards, Tom Ogilvy "Steve" wrote in message ups.com... The code i found was the one as follows, im a bit of a VBA novice as you probably guessed! Sub removeAllCode() Dim awi 'activeWorkbookItem(index) Dim awcl As Integer 'activeWorkbook Component CountOfLines Dim count As Integer 'how many potential code modules Dim i As Integer 'loop counter On Error Resume Next count = ActiveWorkbook.VBProject.VBComponents.count For i = 1 To count Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i) awcl = awi.CodeModule.CountOfLines awi.CodeModule.DeleteLines 1, awcl Next i Set awi = Nothing ' Release the object End Sub Tom Ogilvy wrote: does the code to remove the macros also remove the modules? If not, you will get that prompt. (for general modules). for sheet modules you will need to make sure they are completely empty - no spaces left or anything like that. -- Regards, Tom Ogilvy "Steve" wrote: Hi All, I have an excel spreadsheet that runs a macro on opening and then within the workbook there are various buttons running further macros (i.e. to delete rows etc). After pressing the final button, i want all evidence of macros to be removed from the sheet. When the sheet is saved, closed and then reopened, i dont want the macro enable/disable to appear. Is this possible?? I have found VBA code to remove all macro code, and when looking in Tools\macros, it appears they have all gone, but after saving, closing and re-opening, i still get the enable/disable popup, suggesting i still have macros within the sheet. Any quick responses will be greatly appreciated, on a tight deadline!! Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove an asigned macros | Excel Discussion (Misc queries) | |||
How can I remove all traces of XLM macros? | Excel Programming | |||
Remove macros from spreadsheet | Excel Discussion (Misc queries) | |||
Remove macros on save as | Excel Programming | |||
Remove Macros from Workbook | Excel Programming |