Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



Using VBA to write VBA code (create multiple buttons)

Posted by Luca on October 11, 2001 3:04 AM
By running the following procedure I should create 2 or more buttons inside one specific sheet but I got Excel error and I don't understand the reason.
It works fine if I generate only one button!!
Who can help me?

Luca


Sub AddComm_button()

Dim code As String
Dim j As Integer

Sheets("Sheet1").Select


For j = 1 To 2
'add button
ActiveSheet.OLEObjects.Add ClassType:="Forms.CommandButton.1", _
Left:=126 * j, Top:=96, Width:=126.75, Height:=25.5

'Code for button
code = ""
code = "Sub CommandButton" & Trim(Str(j)) & "_Click()" & vbCrLf
code = code & " Sheets(""Sheet" & Trim(Str(j)) & """).Activate" & vbCrLf
code = code & "End Sub"

'Write code for button
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CountOfLines + 1, code
End With
Next j
End Sub


Check out our Excel VBA Resources

Re: Using VBA to write VBA code (create multiple buttons)

Posted by Jerid on October 11, 2001 6:23 AM
Luca, I initially had some problems with this also, but I finally got it working. Try this.


Sub AddComm_button()

Dim myButton As New OLEObject
Dim sCode As String
Dim iX As Integer
Dim CurSheet As Worksheet

Set CurSheet = Worksheets("Sheet1")

For iX = 1 To 2
Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
myButton.Left = 126 * iX
myButton.Top = 96
myButton.Width = 126.75
myButton.Height = 25.5

Set myButton = Nothing
Next iX

For iX = 1 To 2
'Code for button
sCode = ""
sCode = "Sub CommandButton" & iX & "_Click()" & vbCrLf
sCode = sCode & " Sheets(""Sheet" & iX & """).Activate" & vbCrLf
sCode = sCode & "End Sub"

'Write code for button
ThisWorkbook.VBProject.VBComponents(CurSheet.Name).Activate

With ThisWorkbook.VBProject.VBComponents(CurSheet.Name).CodeModule
.AddFromString (sCode)
End With

sCode = vbNullString
Next iX

End Sub

Jerid


Re: Still some problems!!!

Posted by Luca on October 11, 2001 6:54 AM
Thanks for your help Jerid.
The your solution works fine only in one special case. If the sheet1 is already activated (by mouse) before running the procedure, then the program crashed again. It's fine in all other case!!
It's strange! Every time the sheet where I want to insert buttons is already activated then procedure doesn't work!
Any idea or solution?

Thanks

Luca Dim myButton As New OLEObject Dim sCode As String Dim iX As Integer Dim CurSheet As Worksheet Set CurSheet = Worksheets("Sheet1") For iX = 1 To 2 Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1") myButton.Left = 126 * iX myButton.Top = 96 myButton.Width = 126.75 myButton.Height = 25.5 Set myButton = Nothing Next iX For iX = 1 To 2 'Code for button sCode = "" sCode = "Sub CommandButton" & iX & "_Click()" & vbCrLf sCode = sCode & " Sheets(""Sheet" & iX & """).Activate" & vbCrLf sCode = sCode & "End Sub" 'Write code for button ThisWorkbook.VBProject.VBComponents(CurSheet.Name).Activate With ThisWorkbook.VBProject.VBComponents(CurSheet.Name).CodeModule .AddFromString (sCode) End With sCode = vbNullString Next iX


Re: Still some problems!!!

Posted by Luca on October 11, 2001 6:54 AM
Thanks for your help Jerid.
The your solution works fine only in one special case. If the sheet1 is already activated (by mouse) before running the procedure, then the program crashed again. It's fine in all other case!!
It's strange! Every time the sheet where I want to insert buttons is already activated then procedure doesn't work!
Any idea or solution?

Thanks

Luca Dim myButton As New OLEObject Dim sCode As String Dim iX As Integer Dim CurSheet As Worksheet Set CurSheet = Worksheets("Sheet1") For iX = 1 To 2 Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1") myButton.Left = 126 * iX myButton.Top = 96 myButton.Width = 126.75 myButton.Height = 25.5 Set myButton = Nothing Next iX For iX = 1 To 2 'Code for button sCode = "" sCode = "Sub CommandButton" & iX & "_Click()" & vbCrLf sCode = sCode & " Sheets(""Sheet" & iX & """).Activate" & vbCrLf sCode = sCode & "End Sub" 'Write code for button ThisWorkbook.VBProject.VBComponents(CurSheet.Name).Activate With ThisWorkbook.VBProject.VBComponents(CurSheet.Name).CodeModule .AddFromString (sCode) End With sCode = vbNullString Next iX


Re: Still some problems!!!

Posted by Jerid on October 11, 2001 7:03 AM
What error message are you getting? I'm not having any problems when I select Sheet1 and then run the procedure. Thanks for your help Jerid. Dim myButton As New OLEObject Dim sCode As String Dim iX As Integer Dim CurSheet As Worksheet Set CurSheet = Worksheets("Sheet1") For iX = 1 To 2 Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1") myButton.Left = 126 * iX myButton.Top = 96 myButton.Width = 126.75 myButton.Height = 25.5 Set myButton = Nothing Next iX For iX = 1 To 2 'Code for button sCode = "" sCode = "Sub CommandButton" & iX & "_Click()" & vbCrLf sCode = sCode & " Sheets(""Sheet" & iX & """).Activate" & vbCrLf sCode = sCode & "End Sub" 'Write code for button ThisWorkbook.VBProject.VBComponents(CurSheet.Name).Activate With ThisWorkbook.VBProject.VBComponents(CurSheet.Name).CodeModule .AddFromString (sCode) End With sCode = vbNullString Next iX


Re: No more problems!!!

Posted by Luca on October 11, 2001 9:15 AM
Now it's fine!!!

Thanks a lot Jerid.

Luca What error message are you getting? I'm not having any problems when I select Sheet1 and then run the procedure. : Thanks for your help Jerid. Dim myButton As New OLEObject Dim sCode As String Dim iX As Integer Dim CurSheet As Worksheet Set CurSheet = Worksheets("Sheet1") For iX = 1 To 2 Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1") myButton.Left = 126 * iX myButton.Top = 96 myButton.Width = 126.75 myButton.Height = 25.5 Set myButton = Nothing Next iX For iX = 1 To 2 'Code for button sCode = "" sCode = "Sub CommandButton" & iX & "_Click()" & vbCrLf sCode = sCode & " Sheets(""Sheet" & iX & """).Activate" & vbCrLf sCode = sCode & "End Sub" 'Write code for button ThisWorkbook.VBProject.VBComponents(CurSheet.Name).Activate With ThisWorkbook.VBProject.VBComponents(CurSheet.Name).CodeModule .AddFromString (sCode) End With sCode = vbNullString Next iX



Re: No more problems!!!

Posted by Luca on October 11, 2001 9:15 AM
Now it's fine!!!

Thanks a lot Jerid.

Luca What error message are you getting? I'm not having any problems when I select Sheet1 and then run the procedure. : Thanks for your help Jerid. Dim myButton As New OLEObject Dim sCode As String Dim iX As Integer Dim CurSheet As Worksheet Set CurSheet = Worksheets("Sheet1") For iX = 1 To 2 Set myButton = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1") myButton.Left = 126 * iX myButton.Top = 96 myButton.Width = 126.75 myButton.Height = 25.5 Set myButton = Nothing Next iX For iX = 1 To 2 'Code for button sCode = "" sCode = "Sub CommandButton" & iX & "_Click()" & vbCrLf sCode = sCode & " Sheets(""Sheet" & iX & """).Activate" & vbCrLf sCode = sCode & "End Sub" 'Write code for button ThisWorkbook.VBProject.VBComponents(CurSheet.Name).Activate With ThisWorkbook.VBProject.VBComponents(CurSheet.Name).CodeModule .AddFromString (sCode) End With sCode = vbNullString Next iX





This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.