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

Hi Chuck,

Look at C. Pearson's site:

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

Hopes this helps.
....
Per

"crarbo1" skrev i meddelelsen
...
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

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

I tried your modified code, and it seems to work here.

Which line is causing the error? (Hit Debug, and see which line is
highlighted)

//Per

"crarbo1" skrev i meddelelsen
...
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

Thanks for checking this out for me. However, the error box only lets
me select End or Help. It doesn't give me the option to debug. Any
ideas?
Thanks,
Chuck
On Jul 19, 2:25*pm, "Per Jessen" wrote:
I tried your modified code, and it seems to work here.

Which line is causing the error? (Hit Debug, and see which line is
highlighted)

//Per

"crarbo1" skrev i ...
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

I suspect you have some other code which is interfering with this macro. To
verify that, try to run your macro in a new workbook, and see if it is
working without problems.

If you want you can mail me the workbook and I will give it a look.

Regards,
Per

"crarbo1" skrev i meddelelsen
...
Thanks for checking this out for me. However, the error box only lets
me select End or Help. It doesn't give me the option to debug. Any
ideas?
Thanks,
Chuck
On Jul 19, 2:25 pm, "Per Jessen" wrote:
I tried your modified code, and it seems to work here.

Which line is causing the error? (Hit Debug, and see which line is
highlighted)

//Per

"crarbo1" skrev i
...
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

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

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

Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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

Keiji,
I am making reference to the correct one. I have sent my
spreadsheet to someone else and it is working for them. So, I'm at a
loss.

Thanks,
Chuck
On Jul 21, 8:58*am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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

On 23 Lug, 02:34, crarbo1 wrote:
Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.

Thanks,
Chuck
On Jul 21, 8:58*am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:



Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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- Nascondi testo citato

- Mostra testo citato -

Hi Crarbo1.
Seems you have lost some underscore (continuation) in this
instruction:

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)

The routine work right for me.
Regards
Eliano

eliano,
Thanks for your input. However, that was just a formatting thing in
this group. The real code is correct. Not sure why it didn't get
transferred correctly.

Thanks,
Chuck
On Jul 22, 9:04*pm, eliano wrote:
On 23 Lug, 02:34, crarbo1 wrote:



Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.

Thanks,
Chuck
On Jul 21, 8:58*am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:

Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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- Nascondi testo citato

- Mostra testo citato -

Hi Crarbo1.
Seems you have lost some underscore (continuation) in this
instruction:

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)

The routine work right *for me.
Regards
Eliano

I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.

Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Sub MsgHello()"
LineNum = LineNum + 1
.InsertLines LineNum, "msgbox ""Hello"""
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"
End With
End Sub

Keiji

crarbo1 wrote:
Keiji,
I am making reference to the correct one. I have sent my
spreadsheet to someone else and it is working for them. So, I'm at a
loss.

Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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

keiji,
Thanks for you help. I will try to do what you suggest. I won't be
able to do it until after I get home from work so it will be a while
before I respond. By the way, what I want to do is write the code to
a button in a worksheet, not the module. Not sure if that is an issue
or not but I don't think it should be.

Thanks,
Chuck
On Jul 23, 12:58*am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.

Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

* * *Set VBProj = ActiveWorkbook.VBProject
* * *Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
* * *Set CodeMod = VBComp.CodeModule

* * *With CodeMod
* * * * *LineNum = .CountOfLines + 1
* * * * *.InsertLines LineNum, "Sub MsgHello()"
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "msgbox ""Hello"""
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "End Sub"
* * *End With
End Sub

Keiji

crarbo1 wrote:
Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.

Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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

I know that you want to write a code in a worksheet. if you couldn't
write a code into a module, I think you couldn't also write a code in a
worksheet. if succeed, then change the line below
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
to
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
and check the code could write a macro in worksheets("Sheet1").

Keiji

crarbo1 wrote:
keiji,
Thanks for you help. I will try to do what you suggest. I won't be
able to do it until after I get home from work so it will be a while
before I respond. By the way, what I want to do is write the code to
a button in a worksheet, not the module. Not sure if that is an issue
or not but I don't think it should be.

Thanks,
Chuck
On Jul 23, 12:58 am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.

Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
Set CodeMod = VBComp.CodeModule

With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Sub MsgHello()"
LineNum = LineNum + 1
.InsertLines LineNum, "msgbox ""Hello"""
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"
End With
End Sub

Keiji

crarbo1 wrote:
Keiji,
I am making reference to the correct one. I have sent my
spreadsheet to someone else and it is working for them. So, I'm at a
loss.
Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.
Keiji
crarbo1 wrote:
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

On 23 Lug, 13:59, crarbo1 wrote:
keiji,
* Thanks for you help. *I will try to do what you suggest. *I won't be
able to do it until after I get home from work so it will be a while
before I respond. *By the way, what I want to do is write the code to
a button in a worksheet, not the module. *Not sure if that is an issue
or not but I don't think it should be.

Thanks,
Chuck
On Jul 23, 12:58*am, keiji kounoike <"kounoike A | T



ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.

Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

* * *Set VBProj = ActiveWorkbook.VBProject
* * *Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
* * *Set CodeMod = VBComp.CodeModule

* * *With CodeMod
* * * * *LineNum = .CountOfLines + 1
* * * * *.InsertLines LineNum, "Sub MsgHello()"
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "msgbox ""Hello"""
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "End Sub"
* * *End With
End Sub

Keiji

crarbo1 wrote:
Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.

Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.

Keiji

crarbo1 wrote:
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- Nascondi testo citato

- Mostra testo citato -

No problem, Chuck.
Replace:
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
With:
Set VBComp = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)
Eliano

On 23 Lug, 14:43, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I know that you want to write a code in a worksheet. if you couldn't
write a code into a module, I think you couldn't also write a code in a
worksheet. if succeed, then change the line below
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
to
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
and check the code could write a macro in worksheets("Sheet1").

Keiji



crarbo1 wrote:
keiji,
* Thanks for you help. *I will try to do what you suggest. *I won't be
able to do it until after I get home from work so it will be a while
before I respond. *By the way, what I want to do is write the code to
a button in a worksheet, not the module. *Not sure if that is an issue
or not but I don't think it should be.

Thanks,
Chuck
On Jul 23, 12:58 am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.

Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

* * *Set VBProj = ActiveWorkbook.VBProject
* * *Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
* * *Set CodeMod = VBComp.CodeModule

* * *With CodeMod
* * * * *LineNum = .CountOfLines + 1
* * * * *.InsertLines LineNum, "Sub MsgHello()"
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "msgbox ""Hello"""
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "End Sub"
* * *End With
End Sub

Keiji

crarbo1 wrote:
Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.
Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.
Keiji
crarbo1 wrote:
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- Nascondi testo citato

- Mostra testo citato -

Keiji, sorry for crossing; I had not see your post.
Eliano

I'm also not sure what to think about this situation.
Will the code below work on your PC?

Sub CreateNewGreen1()
Dim ObjNG As Object
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Sheet1.Select
On Error Resume Next
Set ObjNG = ActiveSheet.OLEObjects.ShapeRange("GreenFlag")
If ObjNG Is Nothing Then
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
End If

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule

On Error GoTo 0

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

Keiji

crarbo1 wrote:
keiji,
I did what you suggested and I was able to do it. So, I'm not sure
what to think now.

Thanks for any additional suggestions,
Chuck
On Jul 23, 8:43 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I know that you want to write a code in a worksheet. if you couldn't
write a code into a module, I think you couldn't also write a code in a
worksheet. if succeed, then change the line below
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
to
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
and check the code could write a macro in worksheets("Sheet1").

Keiji

crarbo1 wrote:
keiji,
Thanks for you help. I will try to do what you suggest. I won't be
able to do it until after I get home from work so it will be a while
before I respond. By the way, what I want to do is write the code to
a button in a worksheet, not the module. Not sure if that is an issue
or not but I don't think it should be.
Thanks,
Chuck
On Jul 23, 12:58 am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.
Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Sub MsgHello()"
LineNum = LineNum + 1
.InsertLines LineNum, "msgbox ""Hello"""
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"
End With
End Sub
Keiji
crarbo1 wrote:
Keiji,
I am making reference to the correct one. I have sent my
spreadsheet to someone else and it is working for them. So, I'm at a
loss.
Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.
Keiji
crarbo1 wrote:
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

keiji,
The code below works on a fresh worksheet. Incidentally, I found
out if I run the macro that I thought was crashing the workbook, using
Alt + F8 and then running CreateNewGreen macro, it works. Just not
with the existing code. Not sure what that would behave that way.

Chuck
On Jul 24, 5:05*am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I'm also not sure what to think about this situation.
Will the code below work on your PC?

Sub CreateNewGreen1()
Dim ObjNG As Object
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Sheet1.Select
On Error Resume Next
Set ObjNG = ActiveSheet.OLEObjects.ShapeRange("GreenFlag")
If ObjNG Is Nothing Then
* * *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
End If

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule

On Error GoTo 0

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

Keiji

crarbo1 wrote:
keiji,
* I did what you suggested and I was able to do it. *So, I'm not sure
what to think now.

Thanks for any additional suggestions,
Chuck
On Jul 23, 8:43 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
I know that you want to write a code in a worksheet. if you couldn't
write a code into a module, I think you couldn't also write a code in a
worksheet. if succeed, then change the line below
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
to
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
and check the code could write a macro in worksheets("Sheet1").

Keiji

crarbo1 wrote:
keiji,
* Thanks for you help. *I will try to do what you suggest. *I won't be
able to do it until after I get home from work so it will be a while
before I respond. *By the way, what I want to do is write the code to
a button in a worksheet, not the module. *Not sure if that is an issue
or not but I don't think it should be.
Thanks,
Chuck
On Jul 23, 12:58 am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.
Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
* * *Set VBProj = ActiveWorkbook.VBProject
* * *Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
* * *Set CodeMod = VBComp.CodeModule
* * *With CodeMod
* * * * *LineNum = .CountOfLines + 1
* * * * *.InsertLines LineNum, "Sub MsgHello()"
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "msgbox ""Hello"""
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "End Sub"
* * *End With
End Sub
Keiji
crarbo1 wrote:
Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.
Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne..jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.
Keiji
crarbo1 wrote:
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

test the code below on new workbook. if it success, then try this one on
your workbook with problems. if this fails, then check if the added
worksheet name and worksheet object's name(codename) is the same. In
VBE, you can see something like sheet1(sheet1) in project explore.
sheet1 without parentheses is the object name(codename) and sheet1 with
parentheses is the worksheet name.

Sub macrotest()
macro1
macro2
End Sub

Sub macro1()
MsgBox "Macro1"
End Sub

Sub macro2()
MsgBox "macro2"
Worksheets.Add
macro3
End Sub

Sub macro3()
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 = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)
Set CodeMod = VBComp.CodeModule

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

Keiji

crarbo1 wrote:
keiji,
The code below works on a fresh worksheet. Incidentally, I found
out if I run the macro that I thought was crashing the workbook, using
Alt + F8 and then running CreateNewGreen macro, it works. Just not
with the existing code. Not sure what that would behave that way.

Chuck
On Jul 24, 5:05 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I'm also not sure what to think about this situation.
Will the code below work on your PC?

Sub CreateNewGreen1()
Dim ObjNG As Object
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Sheet1.Select
On Error Resume Next
Set ObjNG = ActiveSheet.OLEObjects.ShapeRange("GreenFlag")
If ObjNG Is Nothing Then
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
End If

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule

On Error GoTo 0

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

Keiji

crarbo1 wrote:
keiji,
I did what you suggested and I was able to do it. So, I'm not sure
what to think now.
Thanks for any additional suggestions,
Chuck
On Jul 23, 8:43 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
I know that you want to write a code in a worksheet. if you couldn't
write a code into a module, I think you couldn't also write a code in a
worksheet. if succeed, then change the line below
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
to
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
and check the code could write a macro in worksheets("Sheet1").
Keiji
crarbo1 wrote:
keiji,
Thanks for you help. I will try to do what you suggest. I won't be
able to do it until after I get home from work so it will be a while
before I respond. By the way, what I want to do is write the code to
a button in a worksheet, not the module. Not sure if that is an issue
or not but I don't think it should be.
Thanks,
Chuck
On Jul 23, 12:58 am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.
Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Sub MsgHello()"
LineNum = LineNum + 1
.InsertLines LineNum, "msgbox ""Hello"""
LineNum = LineNum + 1
.InsertLines LineNum, "End Sub"
End With
End Sub
Keiji
crarbo1 wrote:
Keiji,
I am making reference to the correct one. I have sent my
spreadsheet to someone else and it is working for them. So, I'm at a
loss.
Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.
Keiji
crarbo1 wrote:
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

keiji,
I tried your code in my workbook that is giving me problems. It
almost works. It doesn't crash now but gives me the original error
again: Run-time error '9': Subscript out of range. When I go to
debug, it is on the following line:
Set VBComp = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)

I set this up using the worksheet that I had created instead of yours,
so my ActiveSheet.Name is "7-24-2009 10.34 PM_Fe"

I don't know if any of this is helpfull, but it appears that we are
getting closer to a resolution.

Thanks for the help,
Chuck

On Jul 24, 8:36*pm, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
test the code below on new workbook. if it success, then try this one on
your workbook with problems. if this fails, then check if the added
worksheet name and worksheet object's name(codename) is the same. In
VBE, you can see something like sheet1(sheet1) in project explore.
sheet1 without parentheses is the object name(codename) and sheet1 with
parentheses is the worksheet name.

Sub macrotest()
* * *macro1
* * *macro2
End Sub

Sub macro1()
* * *MsgBox "Macro1"
End Sub

Sub macro2()
* * *MsgBox "macro2"
* * *Worksheets.Add
* * *macro3
End Sub

Sub macro3()
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 = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)
Set CodeMod = VBComp.CodeModule

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

Keiji

crarbo1 wrote:
keiji,
* The code below works on a fresh worksheet. *Incidentally, I found
out if I run the macro that I thought was crashing the workbook, using
Alt + F8 and then running CreateNewGreen macro, it works. *Just not
with the existing code. *Not sure what that would behave that way.

Chuck
On Jul 24, 5:05 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
I'm also not sure what to think about this situation.
Will the code below work on your PC?

Sub CreateNewGreen1()
Dim ObjNG As Object
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long

Sheet1.Select
On Error Resume Next
Set ObjNG = ActiveSheet.OLEObjects.ShapeRange("GreenFlag")
If ObjNG Is Nothing Then
* * *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
End If

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule

On Error GoTo 0

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

Keiji

crarbo1 wrote:
keiji,
* I did what you suggested and I was able to do it. *So, I'm not sure
what to think now.
Thanks for any additional suggestions,
Chuck
On Jul 23, 8:43 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
I know that you want to write a code in a worksheet. if you couldn't
write a code into a module, I think you couldn't also write a code in a
worksheet. if succeed, then change the line below
Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
to
Set VBComp = ActiveWorkbook.VBProject.VBComponents("Sheet1")
and check the code could write a macro in worksheets("Sheet1").
Keiji
crarbo1 wrote:
keiji,
* Thanks for you help. *I will try to do what you suggest. *I won't be
able to do it until after I get home from work so it will be a while
before I respond. *By the way, what I want to do is write the code to
a button in a worksheet, not the module. *Not sure if that is an issue
or not but I don't think it should be.
Thanks,
Chuck
On Jul 23, 12:58 am, keiji kounoike <"kounoike A | T
ma.Pikara.ne.jp" wrote:
I have no idea. For checking your excel, write a macro like below in
Module1 and run the macro write2module and see if a macro MsgHello is
written into Module1. i think your code also should work if this could
work without problem.
Sub write2module()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
* * *Set VBProj = ActiveWorkbook.VBProject
* * *Set VBComp = ActiveWorkbook.VBProject.VBComponents("module1")
* * *Set CodeMod = VBComp.CodeModule
* * *With CodeMod
* * * * *LineNum = .CountOfLines + 1
* * * * *.InsertLines LineNum, "Sub MsgHello()"
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "msgbox ""Hello"""
* * * * *LineNum = LineNum + 1
* * * * *.InsertLines LineNum, "End Sub"
* * *End With
End Sub
Keiji
crarbo1 wrote:
Keiji,
* I am making reference to the correct one. *I have sent my
spreadsheet to someone else and it is working for them. *So, I'm at a
loss.
Thanks,
Chuck
On Jul 21, 8:58 am, keiji kounoike <"kounoike A | T *ma.Pikara.ne.jp"
wrote:
Sorry for the inconvenience. I'm using Excel2003.
By the way, Are you sure that you make reference to Microsoft Visual
Basic for Applications Extensibility 5.3? There is some other name
similar to this one like Microsoft Visual Basic *.* Extensibility. if
you make reference to this one, i think your code would not work correctly.
Keiji
crarbo1 wrote:
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

...

read more »

My newsreader can't see your post, so I posted from Discussion Group.
It seems that your worksheet's name and worksheet's codename don't have the
same name. I may be wrong, but this seems to cause the trouble. you could get
around this problem if you could use Activesheet.Codename, but you said you
can't. I don't know this is the right way to go, but try this one. I changed
macro2 and macro3 to macro21 and macro31 respectively.

Sub macrotest1()
macro1
macro21
End Sub

Sub macro1()
MsgBox "Macro1"
End Sub

Sub macro21()
Dim codelist() As String
Dim i As Long
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

MsgBox "macro21"

With ActiveWorkbook
ReDim codelist(.VBProject.VBComponents.Count - 1)
For i = 0 To .VBProject.VBComponents.Count - 1
codelist(i) = .VBProject.VBComponents(i + 1).Name
Next
End With

Worksheets.Add
ActiveSheet.Name = "7-24-2009 10.34 PM_Fe"

macro31 codelist

End Sub

Sub macro31(codelist)
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

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If IsError(Application.Match(VBComp.Name, codelist, 0)) Then
Set CodeMod = VBComp.CodeModule
Exit For
End If
Next

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

End Sub

Keiji

"crarbo1" wrote:
keiji,
I tried your code in my workbook that is giving me problems. It
almost works. It doesn't crash now but gives me the original error
again: Run-time error '9': Subscript out of range. When I go to
debug, it is on the following line:
Set VBComp = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)

I set this up using the worksheet that I had created instead of yours,
so my ActiveSheet.Name is "7-24-2009 10.34 PM_Fe"

I don't know if any of this is helpfull, but it appears that we are
getting closer to a resolution.

Thanks for the help,
Chuck

Keiji,
I'm not sure if I said I couldn't use Activesheet.Codename or not.
But I don't see why I can't. I will try your code but the sheetname
that you put in your code is not going to be correct. I create that
worksheet once a button is clicked and it renames the worksheet based
on selections made and the time. So, the time would be off. I can
try running the code but not sure if it is going to be realistic to
what I need it to do.

Thanks,
Chuck
On Jul 26, 6:58*am, Keiji Kounoike <Keiji
wrote:
My newsreader can't see your post, so I posted from Discussion Group.
It seems that your worksheet's name and worksheet's codename don't have the
same name. I may be wrong, but this seems to cause the trouble. you could get
around this problem if you could use Activesheet.Codename, but you said you
can't. I don't know this is the right way to go, but try this one. I changed
macro2 and macro3 to macro21 and macro31 respectively.

Sub macrotest1()
* * macro1
* * macro21
End Sub

Sub macro1()
* * MsgBox "Macro1"
End Sub

Sub macro21()
Dim codelist() As String
Dim i As Long
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

MsgBox "macro21"

With ActiveWorkbook
ReDim codelist(.VBProject.VBComponents.Count - 1)
For i = 0 To .VBProject.VBComponents.Count - 1
* * codelist(i) = .VBProject.VBComponents(i + 1).Name
Next
End With

Worksheets.Add
ActiveSheet.Name = "7-24-2009 10.34 PM_Fe"

macro31 codelist

End Sub

Sub macro31(codelist)
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

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
* * If IsError(Application.Match(VBComp.Name, codelist, 0)) Then
* * * * Set CodeMod = VBComp.CodeModule
* * * * Exit For
* * End If
Next

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

End Sub

Keiji

"crarbo1" wrote:
keiji,
* I tried your code in my workbook that is giving me problems. *It
almost works. *It doesn't crash now but gives me the original error
again: Run-time error '9': Subscript out of range. *When I go to
debug, it is on the following line:
Set VBComp = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)

I set this up using the worksheet that I had created instead of yours,
so my ActiveSheet.Name is "7-24-2009 10.34 PM_Fe"

I don't know if any of this is helpfull, but it appears that we are
getting closer to a resolution.

Thanks for the help,
Chuck

Keiji,
I have good news, it works, at least for now. I'm not sure why this
worked but it did. I commented out the Sub CreateNewGreen() code,
this is the code that creates the Green Flag button that has the code
to create the macro for the button. I then created the new worksheet,
that worked as expected and the new worksheet was the active sheet
after it was created. I then went to ToolsMacroMacros, selected the
CreateNewGreen macro and clicked run. Once doing that, the button was
created and the Private Sub GreenFlag_Click() macro was created
correctly and it worked as expected. I didn't know why this was not
working when I call the CreateNewGreen macro from the
CreateNewWorksheet macro. I'm calling it at the very end of that
macro. I wanted to un-comment out the CreateNewGreen macro from the
CreateNewWorksheet macro and try one more time like I thought it
should be. When I did that and ran it from the beginning it worked
without any issues. At least for now. Is that not just weird? I
hope this problem will not reintroduce itself again but I wanted to
thank you very much for helping me out with this.

Chuck
On Jul 26, 6:58*am, Keiji Kounoike <Keiji
wrote:
My newsreader can't see your post, so I posted from Discussion Group.
It seems that your worksheet's name and worksheet's codename don't have the
same name. I may be wrong, but this seems to cause the trouble. you could get
around this problem if you could use Activesheet.Codename, but you said you
can't. I don't know this is the right way to go, but try this one. I changed
macro2 and macro3 to macro21 and macro31 respectively.

Sub macrotest1()
* * macro1
* * macro21
End Sub

Sub macro1()
* * MsgBox "Macro1"
End Sub

Sub macro21()
Dim codelist() As String
Dim i As Long
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

MsgBox "macro21"

With ActiveWorkbook
ReDim codelist(.VBProject.VBComponents.Count - 1)
For i = 0 To .VBProject.VBComponents.Count - 1
* * codelist(i) = .VBProject.VBComponents(i + 1).Name
Next
End With

Worksheets.Add
ActiveSheet.Name = "7-24-2009 10.34 PM_Fe"

macro31 codelist

End Sub

Sub macro31(codelist)
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

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
* * If IsError(Application.Match(VBComp.Name, codelist, 0)) Then
* * * * Set CodeMod = VBComp.CodeModule
* * * * Exit For
* * End If
Next

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

End Sub

Keiji

"crarbo1" wrote:
keiji,
* I tried your code in my workbook that is giving me problems. *It
almost works. *It doesn't crash now but gives me the original error
again: Run-time error '9': Subscript out of range. *When I go to
debug, it is on the following line:
Set VBComp = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)

I set this up using the worksheet that I had created instead of yours,
so my ActiveSheet.Name is "7-24-2009 10.34 PM_Fe"

I don't know if any of this is helpfull, but it appears that we are
getting closer to a resolution.

Thanks for the help,
Chuck

Hi crabo1

I haven't done anything to solve your problem. But I'm glad to hear you
solved the problem yourself.

Keiji

crarbo1 wrote:
Keiji,
I have good news, it works, at least for now. I'm not sure why this
worked but it did. I commented out the Sub CreateNewGreen() code,
this is the code that creates the Green Flag button that has the code
to create the macro for the button. I then created the new worksheet,
that worked as expected and the new worksheet was the active sheet
after it was created. I then went to ToolsMacroMacros, selected the
CreateNewGreen macro and clicked run. Once doing that, the button was
created and the Private Sub GreenFlag_Click() macro was created
correctly and it worked as expected. I didn't know why this was not
working when I call the CreateNewGreen macro from the
CreateNewWorksheet macro. I'm calling it at the very end of that
macro. I wanted to un-comment out the CreateNewGreen macro from the
CreateNewWorksheet macro and try one more time like I thought it
should be. When I did that and ran it from the beginning it worked
without any issues. At least for now. Is that not just weird? I
hope this problem will not reintroduce itself again but I wanted to
thank you very much for helping me out with this.

Chuck
On Jul 26, 6:58 am, Keiji Kounoike <Keiji
wrote:
My newsreader can't see your post, so I posted from Discussion Group.
It seems that your worksheet's name and worksheet's codename don't have the
same name. I may be wrong, but this seems to cause the trouble. you could get
around this problem if you could use Activesheet.Codename, but you said you
can't. I don't know this is the right way to go, but try this one. I changed
macro2 and macro3 to macro21 and macro31 respectively.

Sub macrotest1()
macro1
macro21
End Sub

Sub macro1()
MsgBox "Macro1"
End Sub

Sub macro21()
Dim codelist() As String
Dim i As Long
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

MsgBox "macro21"

With ActiveWorkbook
ReDim codelist(.VBProject.VBComponents.Count - 1)
For i = 0 To .VBProject.VBComponents.Count - 1
codelist(i) = .VBProject.VBComponents(i + 1).Name
Next
End With

Worksheets.Add
ActiveSheet.Name = "7-24-2009 10.34 PM_Fe"

macro31 codelist

End Sub

Sub macro31(codelist)
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

For Each VBComp In ActiveWorkbook.VBProject.VBComponents
If IsError(Application.Match(VBComp.Name, codelist, 0)) Then
Set CodeMod = VBComp.CodeModule
Exit For
End If
Next

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

End Sub

Keiji

"crarbo1" wrote:
keiji,
I tried your code in my workbook that is giving me problems. It
almost works. It doesn't crash now but gives me the original error
again: Run-time error '9': Subscript out of range. When I go to
debug, it is on the following line:
Set VBComp = ActiveWorkbook.VBProject.VBComponents(ActiveSheet. Name)
I set this up using the worksheet that I had created instead of yours,
so my ActiveSheet.Name is "7-24-2009 10.34 PM_Fe"
I don't know if any of this is helpfull, but it appears that we are
getting closer to a resolution.
Thanks for the help,
Chuck

Keiji,
I guess you didn't actually solve my problem, but you helped me out
and for that I thank you.
Chuck
On Jul 26, 8:35*pm, keiji kounoike <"kounoike A | T ma.Pikara.ne.jp"
wrote:
Hi crabo1

I haven't done anything to solve your problem. But I'm glad to hear you
solved the problem yourself.

Keiji