Macro creation via VBA

[crarbo1]

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

 

[Per Jessen]

Hi Chuck,

Look at C. Pearson's site:

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

Hopes this helps.
....
Per

 

[crarbo1]

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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[Per Jessen]

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" <[email protected]> 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
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

[crarbo1]

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

 

[Per Jessen]

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" <[email protected]> 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

 

[keiji kounoike]

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]

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

 

[keiji kounoike]

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]

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

 

[eliano]

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


- 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

 

[crarbo1]

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

 

[keiji kounoike]

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]

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

 

[keiji kounoike]

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

 

[eliano]

keiji,
  Thanks for you help.  I will try to do what you suggest.  I won'tbe
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





- Mostra testo citato -

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

 

[eliano]

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




- Mostra testo citato -

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

 

[crarbo1]

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

 

[keiji kounoike]

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]

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