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,

Rather than simply stating that it is no longer working, it would
be helpful if you would indicate the exact error message and the
line of code that is causing the error.


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



"Stuart Bisset" wrote in message
...

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!

Having tested this a little it would appear that the problem occurs (or
not) depending on whether the VB project is password protected, saved
and closed (or not).

If the user is using the workbook the VBE will not be open and the VB
project will be closed and protected. I assume that this is the reason
the procedure can not add additional code to the project. Is there a
way round this??

Cheers


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

Chip / Anyone

The problem at the beginning of this thread was a "subscript out of
range error" on the following line of code:

With ActiveWorkbook.VBProject. _
VBComponents(shtNewNotesSheet.Name).CodeModule

Chip suggested changing .name to .codename which seemed to work for a
short while. On testing I got other error messages which was to do
with the VBproject being protected, and hence no code could be added
to it. I have now resolved that problem using some SendKeys code
obtained from the newsgroup. HOWEVER I am now getting the same error
noted above whether I use .name or .codename.

Can anyone help me please?

The error reported initially was one of the subscript being out of
range. This one occurs if the name of the Excel sheet has been
changed in Excel at the sheet tab and therefore the name in the sheet
tab is not the same as the name displayed in the VBE list of the
workbook sheets.

The sheetnames in the VBE project sheet list will show the name given
by the user to the sheet tab - which really is only a change in the
caption! - and in brackets the actual name of the sheet (unchanged
when the name in the tab is changed).

So, either address the actual sheet name in your code, or change the
actual sheet name in the VBE to match the name you gave the sheet on
the tab.

This should resolve the subscript out of range problem for you.




Stuart Bisset wrote in message ...
Having tested this a little it would appear that the problem occurs (or
not) depending on whether the VB project is password protected, saved
and closed (or not).

If the user is using the workbook the VBE will not be open and the VB
project will be closed and protected. I assume that this is the reason
the procedure can not add additional code to the project. Is there a
way round this??

Cheers


*** 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
.