Self deleting Macro!!! is this possible?

[bronwyn]

Is it possible to have a macro or vba code that will delete all other macros in a workbook. I have tried searching the threads and found one self deleting macro, I think this is what I need, but I dont really understand what is happening. What I really would like is a macro that can delete all other macros within a workbook, even if macro security is high!...any help would be greatly appreciated


thanks in advance bronny

 

[immyjimmy]

Greg,

In trying your code, I get the following error:

Compile error:
User-defined type not defined

And the line:

VBComp As VBIDE.VBComponent

is highlighted.

This came up as a number of folks here use a file I created. When finished they click a button that emails the file to the lady who does the data entry into the AS-400. When she gets it, she doesn't want to deal with the macros, so I'm trying to get rid of them at the point it gets sent to her...

Any thoughts?

Thanks,
Jim

 

[Greg Truby]

Ermmm, which code was that?

As far as the problem you describe, do you have a refernce to the VBIDE library set in that workbook? (Should be listed as Microsoft Visual Basic for Applications Extensibility or something similar.)

 

[immyjimmy]

Greg,

On your original response:
http://www.cpearson.com/excel/vbe.htm

Use your FIND tool in IE to jump to the section labeled "Deleting All VBA Code In A Project"
_________________
Greg
(XL2002-SP2 on Win2000)

I went to the site, did the search and copied the code. When I run the code, I get the error.

Jim

 

[immyjimmy]

Greg,

Here's the code:

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.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


Jim

 

[Greg Truby]

Did you check that reference to the VBIDE that I cited?

And as much as I would love to take credit for some of Chip's handiwork -- all I did was link to it. The code itself is Mr. Pearson's.

 

[immyjimmy]

Sorry... I'm trying to solve this problem today and haven't spent as much time verifying sources. I did try (in the VBA editor) referencing "Microsoft Visual Basic for Applications Extensibility", but got the same error. As there look to be a thousand (actually 379) or so reference libraries to choose from, it looks to be a loooong weekend...

Any further ideas?

Jim

 

[Greg Truby]

If I try to run that code w/o the reference, I get the exact error you describe. So I'm bettin' that's your problem. Did you set the reference and it didn't work? Or did you not find the reference? On my machine the path to that library is:

C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

HTH

 

[immyjimmy]

Greg,

I set the reference, saved my file, ran the macro and got the following error:

Run-time error '1004':
programmatic access to Visual Basic Project is not trusted

with the following line highlighted in the VB editor:
Set VBComps = ActiveWorkbook.VBProject.VBComponents

Since this is a new error message, I'm assuming some progress was made...

Jim

 

[Joe Was]

Jimmy, Thats an easy fix, I think?

In Excel you need to click the Trusted Sources box for the: Visual Basic Project.

To do this:

Tools - Macros... - Security, Then click the Trusted Sources tab.

By default on install or re-install Excel resets this to Do not trust access to the visual basic project!

 

[Greg Truby]

Jim,

Yep, that's a step in the right direction. <ul>[*]Tools | Macro | Security (or if your Excel is quirky like mine, Tools | Options... | Security Tab | Macro Security Button)[*]Click on the Trusted Sources tab.[*]Check the box Trust access to the Visual Basic Project.[/list][Take another look at the code Joe posted in his second post - tells you the same thing. :wink:]

{Edit: Well, looks like Joe just showed up himself and beat me to the punch! }