Delete all code in VBA module - error?

[Matt]

I am trying to use the code:

Sub delete_all_code (module_name)

Dim VBCodeMod as VBComponent
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(module_name)CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With

End Sub

(taken from G.Groups many years ago)

that has worked for me with no problems in the past. I think I was
using excel 2000 when I first used this.

I am now using excel 2003, and i have the VBA Extensibility reference
'activated' . Have also tried unchecking this and using 'Dim VBCodeMod
as Object' with no success.

When I try to run this I get the following error:

Run-time error '1004':

Method 'VBProject' of Object '_Workbook' failed

Any ideas as to how I can get this to work or any other way of deleting
either:

1 - all the code in an excel VBA module
or
2 - the entire VBA module (preferable)

for a given module.

Many thanks in advance for any help!

Matt

 

[Matt]

Thanks, this works fine now on my computer!

BUT

This is part of an application that will be rolled out across multiple
(20+) analysts (the VBA code needs deleting as the output will get sent
to clients) who may not have their security set to this. Is there a
way of changing this setting using VBA or will they just have to change
their own settings?

Thanks

Matt

 

[Ron de Bruin]

Hi Matt

Not possible to change it but you can see if it is checked
See this page
http://www.j-walk.com/ss/excel/tips/tip96.htm