MrExcel.com - Your One Stop for Excel Tips & Solutions

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

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

Posted by Luca on October 11, 2001 6:54 AM

Re: Still some problems!!!

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

Posted by Luca on October 11, 2001 6:54 AM

Re: Still some problems!!!

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

Posted by Jerid on October 11, 2001 7:03 AM

Re: Still some problems!!!

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

Posted by Luca on October 11, 2001 9:15 AM

Re: No more problems!!!

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


Posted by Luca on October 11, 2001 9:15 AM

Re: No more problems!!!

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