I have John Walkenbach's book "Excel 2002 Power Programming with VBA"
and was using the section entitled Using VBA to Write VBA code (pg
797). Unfortunately the code I have entered is not working and I
think it is because of the fact that I have excel 2000 (but I'm not
sure). The line of code which is causing the error is the first one in
the following section

With ActiveWorkbook.VBProject. _
VBcomponents(shtNewNotesSheet.Name).CodeModule
nextline = .CountOfLines + 1
.InsertLines nextline, Code
End With

(where nextline is a numeric variable (dim as double) and where Code
is a string variable which contains the VBA code I wish to insert into
the worksheet)

Is the "VBComponents()" element a new property of the VBProject object
(ie in excel 2002 but not excel 2000)??

If so how can I use VBA to write VBA in excel 2000?

Thanks in advance

What error are you getting? The code you are using should work
in all versions of Excel since Excel97. NextLine should be
defined as a Long not a Double.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"SB" wrote in message
om...
I have John Walkenbach's book "Excel 2002 Power Programming
with VBA"
and was using the section entitled Using VBA to Write VBA code
(pg
797). Unfortunately the code I have entered is not working and
I
think it is because of the fact that I have excel 2000 (but I'm
not
sure). The line of code which is causing the error is the first
one in
the following section

With ActiveWorkbook.VBProject. _
VBcomponents(shtNewNotesSheet.Name).CodeModule
nextline = .CountOfLines + 1
.InsertLines nextline, Code
End With

(where nextline is a numeric variable (dim as double) and where
Code
is a string variable which contains the VBA code I wish to
insert into
the worksheet)

Is the "VBComponents()" element a new property of the VBProject
object
(ie in excel 2002 but not excel 2000)??

If so how can I use VBA to write VBA in excel 2000?

Thanks in advance

Chip

Thx for quick reply. I'm getting a "subscript out of range" error.
Since posting my first message I typed in the code on pg798 of the book
in a new workbook and it worked fine. It just doesn't work on this
workbook. The error relates to the "With Activeworkbook ....
CodeModule" line.

Your assistance is greatly appreciated.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Stuart,

Change

VBcomponents(shtNewNotesSheet.Name).CodeModule

to
VBcomponents(shtNewNotesSheet.CodeName).CodeModule



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Stuart Bisset" wrote in message
...
Chip

Thx for quick reply. I'm getting a "subscript out of range"
error.
Since posting my first message I typed in the code on pg798 of
the book
in a new workbook and it worked fine. It just doesn't work on
this
workbook. The error relates to the "With Activeworkbook ....
CodeModule" line.

Your assistance is greatly appreciated.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thx Chip - That now works

Much Appreciated.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Chip,

Hope you're still out there ... strangely it is no longer working. Do
you have any other suggestions?

Kind Regards


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Stuart,

I've been wrestling with this exact same problem. What I
found is that the code from the book always write the code
routine to Sheet1. If you have a multi-sheet workbook,
this is not what you want. What worked for me is the
following line of code:

With ThisWorkbook.VBProject. _
VBComponents(NewSheet.CodeName).CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code
End With

It seems that .CodeName is the correct reference.

--Frank

-----Original Message-----
I have John Walkenbach's book "Excel 2002 Power
Programming with VBA"
and was using the section entitled Using VBA to Write VBA
code (pg
797). Unfortunately the code I have entered is not
working and I
think it is because of the fact that I have excel 2000
(but I'm not
sure). The line of code which is causing the error is the
first one in
the following section

With ActiveWorkbook.VBProject. _
VBcomponents(shtNewNotesSheet.Name).CodeModule
nextline = .CountOfLines + 1
.InsertLines nextline, Code
End With

(where nextline is a numeric variable (dim as double) and
where Code
is a string variable which contains the VBA code I wish
to insert into
the worksheet)

Is the "VBComponents()" element a new property of the
VBProject object
(ie in excel 2002 but not excel 2000)??

If so how can I use VBA to write VBA in excel 2000?

Thanks in advance
.