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

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


Jim Thomlinson wrote:
> You may need to change your security. Select Tools -> Macros -> Security ->
> Trusted Sources (Tab) -> Trust Access to Visual Basic Projects
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Matt" wrote:
>
> > 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
> >
> >

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Matt" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>
>
> Jim Thomlinson wrote:
>> You may need to change your security. Select Tools -> Macros -> Security ->
>> Trusted Sources (Tab) -> Trust Access to Visual Basic Projects
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "Matt" wrote:
>>
>> > 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
>> >
>> >
>