Deleting a 'workbook' macro

[chiv]

Good day all, I wonder if anyone has come across this before?

I have a 'private sub' macro which runs when a template is opened. The utility of this is to assign an incremental Project ID to the template each time it is opened. Obviously, the template is then saved under a different name...as well as being saved as an .xls file.

The problem: The private sub code is still there when the new named worksheet is opened and the increment still clicks up one number everytime the sheet is opened. I have been trying to write code to insert after the existing code in the .xlt file which will effectively delete the code once the template has assigned a number.

I am afraid I have not been able to do this, so any help would be greatly appreciated.

Thank you.

[Bob Phillips]

Here is some code to delete the Workbook_Open code

--
HTH

Bob Phillips

"chiv" <[email protected]> wrote in message
news:[email protected]...
>
> Good day all, I wonder if anyone has come across this before?
>
> I have a 'private sub' macro which runs when a template is opened. The
> utility of this is to assign an incremental Project ID to the template
> each time it is opened. Obviously, the template is then saved under a
> different name...as well as being saved as an .xls file.
>
> The problem: The private sub code is still there when the new named
> worksheet is opened and the increment still clicks up one number
> everytime the sheet is opened. I have been trying to write code to
> insert after the existing code in the .xlt file which will effectively
> delete the code once the template has assigned a number.
>
> I am afraid I have not been able to do this, so any help would be
> greatly appreciated.
>
> Thank you.
>
>
> --
> chiv
> ------------------------------------------------------------------------
> chiv's Profile:
http://www.excelforum.com/member.php...o&userid=23400
> View this thread: http://www.excelforum.com/showthread...hreadid=373371
>

[IC]

Bob, there's no code in your post.

Ian

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Here is some code to delete the Workbook_Open code
>
> --
> HTH
>
> Bob Phillips
>
> "chiv" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Good day all, I wonder if anyone has come across this before?
>>
>> I have a 'private sub' macro which runs when a template is opened. The
>> utility of this is to assign an incremental Project ID to the template
>> each time it is opened. Obviously, the template is then saved under a
>> different name...as well as being saved as an .xls file.
>>
>> The problem: The private sub code is still there when the new named
>> worksheet is opened and the increment still clicks up one number
>> everytime the sheet is opened. I have been trying to write code to
>> insert after the existing code in the .xlt file which will effectively
>> delete the code once the template has assigned a number.
>>
>> I am afraid I have not been able to do this, so any help would be
>> greatly appreciated.
>>
>> Thank you.
>>
>>
>> --
>> chiv
>> ------------------------------------------------------------------------
>> chiv's Profile:
> http://www.excelforum.com/member.php...o&userid=23400
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=373371
>>
>
>

[Damien McBain]

"chiv" <[email protected]> wrote in message
news:[email protected]...
>
> Good day all, I wonder if anyone has come across this before?
>
> I have a 'private sub' macro which runs when a template is opened. The
> utility of this is to assign an incremental Project ID to the template
> each time it is opened. Obviously, the template is then saved under a
> different name...as well as being saved as an .xls file.
>
> The problem: The private sub code is still there when the new named
> worksheet is opened and the increment still clicks up one number
> everytime the sheet is opened. I have been trying to write code to
> insert after the existing code in the .xlt file which will effectively
> delete the code once the template has assigned a number.

You could create the new workbook based on another template (xls or xlt)
which has no VBA, save it with the filename you want and close the original
workbook (with the code) all in one hit. So the original workbook would run
the code then close and leave the new saved workbook active.

[Bob Phillips]

Sorry Ian,

Sub DeleteProcedure()
Dim oVBMod As Object
Dim iStart As Long
Dim cLines As Long

Set oVBMod =
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
With oVBMod
iStart = .ProcStartline("Workbook_Open", 0)
cLines = .ProcCountLines("Workbook_Open", 0)
.DeleteLines iStart, cLines
End With

End Sub


--
HTH

Bob Phillips

"IC" <[email protected]> wrote in message
news:[email protected]...
> Bob, there's no code in your post.
>
> Ian
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Here is some code to delete the Workbook_Open code
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "chiv" <[email protected]> wrote in message
> > news:[email protected]...
> >>
> >> Good day all, I wonder if anyone has come across this before?
> >>
> >> I have a 'private sub' macro which runs when a template is opened. The
> >> utility of this is to assign an incremental Project ID to the template
> >> each time it is opened. Obviously, the template is then saved under a
> >> different name...as well as being saved as an .xls file.
> >>
> >> The problem: The private sub code is still there when the new named
> >> worksheet is opened and the increment still clicks up one number
> >> everytime the sheet is opened. I have been trying to write code to
> >> insert after the existing code in the .xlt file which will effectively
> >> delete the code once the template has assigned a number.
> >>
> >> I am afraid I have not been able to do this, so any help would be
> >> greatly appreciated.
> >>
> >> Thank you.
> >>
> >>
> >> --
> >> chiv
>
>> ------------------------------------------------------------------------
> >> chiv's Profile:
> > http://www.excelforum.com/member.php...o&userid=23400
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=373371
> >>
> >
> >
>
>

[IC]

That's OK, Bob. It didn't really worry me but I thought I'd mention it for
chiv's benefit.

Ian

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Sorry Ian,
>
> Sub DeleteProcedure()
> Dim oVBMod As Object
> Dim iStart As Long
> Dim cLines As Long
>
> Set oVBMod =
> ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
> With oVBMod
> iStart = .ProcStartline("Workbook_Open", 0)
> cLines = .ProcCountLines("Workbook_Open", 0)
> .DeleteLines iStart, cLines
> End With
>
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "IC" <[email protected]> wrote in message
> news:[email protected]...
>> Bob, there's no code in your post.
>>
>> Ian
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Here is some code to delete the Workbook_Open code
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "chiv" <[email protected]> wrote in message
>> > news:[email protected]...
>> >>
>> >> Good day all, I wonder if anyone has come across this before?
>> >>
>> >> I have a 'private sub' macro which runs when a template is opened.
>> >> The
>> >> utility of this is to assign an incremental Project ID to the template
>> >> each time it is opened. Obviously, the template is then saved under a
>> >> different name...as well as being saved as an .xls file.
>> >>
>> >> The problem: The private sub code is still there when the new named
>> >> worksheet is opened and the increment still clicks up one number
>> >> everytime the sheet is opened. I have been trying to write code to
>> >> insert after the existing code in the .xlt file which will effectively
>> >> delete the code once the template has assigned a number.
>> >>
>> >> I am afraid I have not been able to do this, so any help would be
>> >> greatly appreciated.
>> >>
>> >> Thank you.
>> >>
>> >>
>> >> --
>> >> chiv
>>
>>> ------------------------------------------------------------------------
>> >> chiv's Profile:
>> > http://www.excelforum.com/member.php...o&userid=23400
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=373371
>> >>
>> >
>> >
>>
>>
>
>