Macro creation via VBA

[keiji kounoike]

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]

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 Kounoike]

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]

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

 

[crarbo1]

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 Tools>Macro>Macros, 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

 

[keiji kounoike]

Hi crabo1

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

Keiji

 

[crarbo1]

Keiji,
I guess you didn't actually solve my problem, but you helped me out
and for that I thank you.
Chuck