create CommandButtons

[feipezihao]

Hi,
I've been trying to create some buttons on a single tab or each button for every tab that I have in a workbook, with the VBA command in VBE codemodule. The button part is fine but evey time as try to write the code for each button (not manually but using VBA), Excel session will be crashed. The code I use goes like this:

Sub AddSomeButtons()
'On Error Resume Next
Application.ScreenUpdating = False
Dim NewButton As OLEObject
For Each c In ThisWorkbook.VBProject.VBComponents
Set WBCodeMod = c.CodeModule
Next
ThisWorkbook.VBProject.VBComponents.CodeModule _
.DeleteLines 1, LineCount

For x = 1 To Worksheets.Count
If Sheets(x).Name <> "contents" Then
Set NewButton = ActiveSheet.OLEObjects.Add _
("Forms.CommandButton.1")
With NewButton
.Left = 4
.Top = 40 * x
.Width = 54
.Height = 20
.Object.Caption = Sheets(x).Name
End With

ThisWorkbook.VBProject.VBComponents.CodeModule _
.AddFromString _
"Private Sub CommandButton" & x & "_Click" & vbCr & _
"Msgbox ""This is CommandButton" & x & """" & vbCr & _
"End Sub"
End If
Next

Application.ScreenUpdating = True
On Error GoTo 0
End Sub

The statement between the frogs is always the trouble-spot. If I don't use the For loop, i.e., create a single button with the code, then fine. Or it crashes.

Thanks in advance for sharing some input if any.
Feipe

[MartinLiss]

Moved.

[RobDog888]

My CodeBank thread should help you to interact with dynamic coding of vba code.

http://www.vbforums.com/showthread.php?t=313861

But looks like your missing the last "()" on your event procedure line.