Using VBA to Write VBA code
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
.
|