Keiji,
I tried your modification and it crashed Excel. I'm running Excel
2000 if that makes a difference. Is CodeName for later versions of
Excel?

Per Jessen,
You are probably correct about being other code causing the
problem. I will try to send you the spreadsheet to you via email
after this post.

I don't have access to groups from work, so it will be after 7pm EST
before I will see any replies.

Thanks to all,
Chuck
On Jul 20, 4:39*am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I may be wrong, but how about changing the line below in your code

Set VBComp = VBProj.VBComponents(ActiveSheet.Name)
to
Set VBComp = VBProj.VBComponents(ActiveSheet.CodeName)

Keiji

crarbo1 wrote:
Thanks for the link. *I checked it out and modified it to what I
thought I needed. However, I get the following error: Run-time error
'9': Subscript out of range

I have attached my code below. Any help is appreciated. Thanks!
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''
Sub CreateNewGreen()
Dim ObjNG As Object
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

* * Set ObjNG = ActiveSheet.OLEObjects.Add
(ClassType:="Forms.CommandButton.1", Link:=False _
* * * * , DisplayAsIcon:=False, Left:=201.75, Top:=12.75,
Width:=99.75, Height:=21.75)
* * ObjNG.Name = "GreenFlag"
* * ActiveSheet.OLEObjects(1).Object.Caption = "Green Flag (Ctrl + g)"
* * ActiveSheet.OLEObjects(1).Object.BackColor = vbGreen

* * Set VBProj = ActiveWorkbook.VBProject
* * Set VBComp = VBProj.VBComponents(ActiveSheet.Name)
* * Set CodeMod = VBComp.CodeModule

* * With CodeMod
* * * * LineNum = .CountOfLines + 1
* * * * .InsertLines LineNum, "Private Sub GreenFlag_Click()"
* * * * LineNum = LineNum + 1
* * * * .InsertLines LineNum, "Green"
* * * * LineNum = LineNum + 1
* * * * .InsertLines LineNum, "End Sub"
* * End With
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''

On Jul 19, 8:41 am, "Per Jessen" wrote:
Hi Chuck,

Look at C. Pearson's site:

http://www.cpearson.com/excel/vbe.aspx

Hopes this helps.
...
Per

"crarbo1" skrev i ...

Hello,
I'm wanting to automatically create a macro in Excel 2000 when I click
a command button. I'm using a command button from the control toolbox..
My example is as follows:
Click a button that is assigned a macro called "Macro1"
"Macro1" then executes some code and a macro called "Macro2"
"Macro2" executes some code that creates a new worksheet, adds a
command button from the control toolbox, names the control button, and
then creates "Macro3" for use of the control button created in
"Macro2" automatically all using vba.
I can do everything above except create "Macro3" automatically using
vba for excel 2000.
I'm sure I'm missing something simple but it is not clicking right now
as to what I need to do.
Can anyone help me out? If you need more info, please let me know.
Thanks,
Chuck