| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro creation via VBA
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro creation via VBA
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro creation help | Excel Programming | |||
| Macro Creation | Excel Programming | |||
| Macro Creation | Excel Discussion (Misc queries) | |||
| Macro Creation | Excel Programming | |||
| XML creation via a macro | Excel Programming | |||
Hybrid Mode
