Remove All Macros

[Steve]

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

 

[Bob Flanagan]

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]

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!!

 

[Steve]

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]

No, that doesn't remove the modules. This link should get you going.

http://www.cpearson.com/excel/vbe.htm