Excel macros won't go away 2

[sksnyder]

I wrote a very basic macro in Excel and then deleted it. But now every time I open the workbook it asks me if I want to enable or disable macros. I've tried going into VB Editor - I deleted all Modules. This is a very large file with links, it would take days to re-create, and I want that annoying pop-up to go away since I share the file with other users. Can anyone help me??? Thanks !

 

[TonyJollans]

Hi sksnyder,

Have you actually deleted the Modules or just the code within them? If you have any modules - even empty ones - Excel will tell you the workbook contains macros.

In the VB Editor make sure the Project Explorer is visible (press Ctrl-R if not). Expand your workbook project; expand Modules if it is present then select each module in turn and then either right click or select the File Menu, then select Remove Module and reply to the prompt (probably No, but it's up to you).

If that doesn't work, or there aren't any modules make sure there is no code in any of the Microsoft Excel Objects listed.

Enjoy,
Tony

 

[APElliott]

Hello,

If you open up Visual Basic and then open The Project Explorer if it isn't already open, and then open up your workbooks Module file. You can then right hand click on the macros you need to remove.

Hope this helps

Andrew

 

[sksnyder]

Thanks for the quick replies, but I'm still stuck. All modules are gone. The only things I see under Project Explorer are AcrobatPDFMaker, Funcres, and VBAProject, which has 10 objects - one for each sheet, plus one called This Workbook. Each of those objects has code under the Worksheet option such as Activate, Calculate, Change, etc. Is that what I'm supposed to get rid of?

Appreciate the assistance.

Steve

 

[TonyJollans]

Hi sksnyder,

Yes, you must clear all that code out and leave all the code objects for your worksheets and workbook completely empty (assuming there's no code in there that you need, of course).

I'm curious - the code in there doesn't get there by itself - what was the 'very basic module' you wrote?

Enjoy,
Tony

 

[willyboy58]

Thanks guys,

I had the same question but never got around to asking.

 

[sksnyder]

OK. The very basic macro was just to edit a character out of a text cell, move to the next cell and repeat. I too am confused on how all that code get there. I will try removing it all from a copy of the file and see if that works. Thanks!!

Steve

 

[sksnyder]

I tried deleting all that Code I mentioned, but it will not let me. So I opened the previous version of the file (the one before I created the macro) and checked the VB code - and it was all there too. It is on every Excel file I have - so I don't think that is the cuplrit. I found the suggestion below elsewhere on the internet, but I don't know how to run it. Any suggestions?

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 &quot;The VBProject in &quot; & objDocument.Name & _
&quot; is protected or has no components!&quot;, _
vbInformation, &quot;Remove All Macros&quot;
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

Thanks !!
Steve

 

[jammastajc]

Is it possible that you added the macro to the global template for all Excel workbooks?

 

[eannmartin]

That sounds like a security issue. If you don't want that message to pop up, change the macro security to low.

You have to control over where other people have their security levels set, so this won't help them. They can set their security levels to low also, but beware of receiving files with macros.

You probably have a add-in or template that is triggering the message.

You can find the security settings through tools/macros/security.

Hope that helps

 

[sksnyder]

Adjusting the security solved the symptom of the pop-up message... not sure I actually fixed the problem though. I do have other add-ins installed which could be causing the issue. I appreciate all the help from everyone that responded.

Thanks
Steve