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
:afrog: ThisWorkbook.VBProject.VBComponents.CodeModule _
.AddFromString _
"Private Sub CommandButton" & x & "_Click" & vbCr & _
"Msgbox ""This is CommandButton" & x & """" & vbCr & _
"End Sub" :afrog:
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
