programming VBE -> Deleting All Code From A Module

[pm]

hello,

i have a file remove.xls in which i use such a code:

Sub DeleteAllCodeInModule()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim HowManyLines As Long

Set VBCodeMod = Workbooks("new.xls").VBProject.VBComponents("Sheets4").CodeModule
With VBCodeMod
StartLine = 1
HowManyLines = .CountOfLines
.DeleteLines StartLine, HowManyLines
End With

End Sub

to remove Private Sub Worksheet_Calculate() in new.xls

than i save and close file, but on reopening new.xls an information about
existing macros is appears and i do not know why?
there is no VBA code in file. i checked carefully other sheets and ThisWorkbook
and they are empty...

does somebody has any idea what is going on? i use office xp..

regards
peter

[Ron de Bruin]

Hi Pm

You must delete the empty module also
http://www.contextures.com/xlfaqMac.html#NoMacros


--
Regards Ron de Bruin
http://www.rondebruin.nl


"pm" <[email protected]> wrote in message news:[email protected]...
> hello,
>
> i have a file remove.xls in which i use such a code:
>
> Sub DeleteAllCodeInModule()
> Dim VBCodeMod As CodeModule
> Dim StartLine As Long
> Dim HowManyLines As Long
>
> Set VBCodeMod = Workbooks("new.xls").VBProject.VBComponents("Sheets4").CodeModule
> With VBCodeMod
> StartLine = 1
> HowManyLines = .CountOfLines
> .DeleteLines StartLine, HowManyLines
> End With
>
> End Sub
>
> to remove Private Sub Worksheet_Calculate() in new.xls
>
> than i save and close file, but on reopening new.xls an information about
> existing macros is appears and i do not know why?
> there is no VBA code in file. i checked carefully other sheets and ThisWorkbook
> and they are empty...
>
> does somebody has any idea what is going on? i use office xp..
>
> regards
> peter

[pm]

Ron de Bruin wrote:

> Hi Pm
>
> You must delete the empty module also
> http://www.contextures.com/xlfaqMac.html#NoMacros

sure but as i wrote i run deleting macro from other file
(remove.xls) in order to delete private sub code in sheets
in new.xls (which is one existing code in this file)

in new.xls there is only 1 code in Sheets4. no free modules

btw here: http://miodek.no-ip.org/~prob/PL_1000_21-12-2005.xls
you can find such a file after deleting private sub from
Sheets5 (revenues), but on when file is opening a warning about
macros occurs... find a VBA code in this file, get beer

[Ken Wright]

When you open that file via the link provided you are prompted with the
macro warning, but once you have saved down the file, closed it and then
reopened it there is no more warning after that.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"pm" <[email protected]> wrote in message news:[email protected]...
> Ron de Bruin wrote:
>
>> Hi Pm
>>
>> You must delete the empty module also
>> http://www.contextures.com/xlfaqMac.html#NoMacros
>
> sure but as i wrote i run deleting macro from other file
> (remove.xls) in order to delete private sub code in sheets
> in new.xls (which is one existing code in this file)
>
> in new.xls there is only 1 code in Sheets4. no free modules
>
> btw here: http://miodek.no-ip.org/~prob/PL_1000_21-12-2005.xls
> you can find such a file after deleting private sub from
> Sheets5 (revenues), but on when file is opening a warning about
> macros occurs... find a VBA code in this file, get beer

[pm]

Ken Wright wrote:

> When you open that file via the link provided you are prompted with the
> macro warning, but once you have saved down the file, closed it and then
> reopened it there is no more warning after that.


so, this is the answer..

thank you