Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get rid of deleted macros in Excel
I have some Excel files that contain Visual Basic macros. When I distribute them to other users, I delete the macros and save the file with a different name. Unfortuantely, when I open the new file, it still shows Macros as being in the file, even though there are none to delete. How can I fix this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get rid of deleted macros in Excel
Check out this site
http://www.contextures.com/xlfaqMac.html#NoMacros -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "donbocce" wrote in message ... I have some Excel files that contain Visual Basic macros. When I distribute them to other users, I delete the macros and save the file with a different name. Unfortuantely, when I open the new file, it still shows Macros as being in the file, even though there are none to delete. How can I fix this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get rid of deleted macros in Excel
You need to get rid of the module that contained the macros. Even an empty
module will trigger the macro warning. "donbocce" wrote in message ... I have some Excel files that contain Visual Basic macros. When I distribute them to other users, I delete the macros and save the file with a different name. Unfortuantely, when I open the new file, it still shows Macros as being in the file, even though there are none to delete. How can I fix this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get rid of deleted macros in Excel
You need to delete any empty modules that housed those macros.
Hit ALT+F11 and this will open the VBE (Visual Basic Editor) Top left you will hopefully see an explorer style pane. Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to expand it. Within that you should see the following:- VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) ThisWorkbook Modules Module1 Module2 etc etc (You may have just 1 of these) If you have named your sheets then those names will appear in the brackets above as opposed to what you see at the moment in my note. Right click on the modules and select remove. When prompted with a question re exporting, just hit no. Then hit File / Close and return to Microsoft Excel and save the file. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference ---------------------------------------------------------------------------- "donbocce" wrote in message ... I have some Excel files that contain Visual Basic macros. When I distribute them to other users, I delete the macros and save the file with a different name. Unfortuantely, when I open the new file, it still shows Macros as being in the file, even though there are none to delete. How can I fix this? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.535 / Virus Database: 330 - Release Date: 01/11/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get rid of deleted macros in Excel
Hi , I dont know where I found this on the web, but the below macro works well to remove all code from the workbook, but after running this you still have to open and do a file save(there is probably a workaround). After that the users will not see the macros enable/disable prompt. _________________________________________ Sub RemoveAllMacros(objDocument As Object) ' deletes all VBProject components from objDocument ' removes the code from built-in components that can't be deleted. ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel ' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word ' requires a reference to the ' Microsoft Visual Basic for Applications Extensibility library Dim i As Long, l As Long If objDocument Is Nothing Then Exit Sub i = 0 On Error Resume Next i = objDocument.VBProject.VBComponents.Count On Error GoTo 0 If i < 1 Then ' no VBComponents or protected VBProject MsgBox "The VBProject in " & objDocument.Name & _ " is protected or has no components!", _ vbInformation, "Remove All Macros" Exit Sub End If With objDocument.VBProject For i = .VBComponents.Count To 1 Step -1 On Error Resume Next ..VBComponents.Remove .VBComponents(i) ' delete the component On Error GoTo 0 Next i End With With objDocument.VBProject For i = .VBComponents.Count To 1 Step -1 l = 1 On Error Resume Next l = .VBComponents(i).CodeModule.CountOfLines ..VBComponents(i).CodeModule.DeleteLines 1, l ' clear lines On Error GoTo 0 Next i End With End Sub _________________________ Example : Call RemoveMacros Activeworkbook ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you eliminate macro warnings if you deleted macros | Excel Discussion (Misc queries) | |||
disable security warning when macros are deleted | Excel Discussion (Misc queries) | |||
Why do macros show up while opening document, but were deleted? | Excel Discussion (Misc queries) | |||
I deleted all macros in Excel but still get dialog box | Excel Discussion (Misc queries) | |||
Deleted Macros? | Excel Discussion (Misc queries) |