Error with creating command button during runtime

Question

I have a module that creates command buttons during run-time. It will create the command buttons in a specified user-form. Program works fine, when I execute the module.

However when I use the user-form to call the module instead, I have a error stating

Run-time error '91': Object variable or With block variable not set

Code

 Sub AddButtonAndShow()

    Dim Butn As CommandButton
    Dim Line As Long
    Dim objForm As Object
    Dim i As Integer
    Dim x As Integer

    Set objForm = ThisWorkbook.VBProject.VBComponents("Current_Stock")



For i = 1 To 3
    Set Butn = objForm.Designer.Controls.Add("Forms.CommandButton.1")
    With Butn
        .Name = "CommandButton" & i
        .Caption = i
        .Width = 100
        .Left = 300
        .Top = 10 * i * 2
    End With
Next

    For x = 1 To 3
        With objForm.CodeModule
            Line = .CountOfLines
            .InsertLines Line + 1, "Sub CommandButton" & x & "_Click()"
            .InsertLines Line + 2, "MsgBox ""Hello!"""
            .InsertLines Line + 3, "End Sub"
      End With
    '
     Next x

End Sub

Kindly advise.

Answer 1

Editted post: based on VBProject Form Components

Earlier post is for Excel Sheet buttons. I noticed for Form buttons you may set the caption by directly referring to the button itself. I tried out your code. Doesn't seem to find any errors. The only addtional things I did was adding the reference Library (MS VB Extensibility 5.3), Private scope to code and combining code into one with statement.

As per this Mr.Excel article, you need add do Events to run the code when VB Editor is closed. Your error seems to be very much alike to what's mentioned in the article.

Revised Code:

Sub AddButtonAndShow()

    Dim Butn As CommandButton
    Dim Line As Long
    Dim objForm As Object
    Dim i As Integer
    Dim x As Integer
    Dim code As String

        Application.DisplayAlerts = False
        DoEvents
        On Error Resume Next
        Set objForm = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
        objForm.Name = "thisform1"

        For i = 1 To 3
            Set Butn = objForm.Designer.Controls.Add("Forms.CommandButton.1")
            With Butn
                    .Name = "CommandButton" & i
                    .Caption = i
                    .Width = 100
                    .Left = 100
                    .Top = (i * 24) + 10

                    Line = objForm.CodeModule.CountOfLines + 1
                    code = "Private Sub " & Butn.Name & "_Click()" & vbCr
                    code = code & "MsgBox ""Hello!""" & vbCr
                    code = code & "End Sub"
                    objForm.CodeModule.InsertLines Line, code
              End With
        Next
        Application.DisplayAlerts = False
   VBA.UserForms.Add(objForm.Name).Show
End Sub

Excel Command Button code:

Private Sub CommandButton3_Click()
Call AddButtonAndShow
End Sub

Output: Use an Excel Sheet button to open the newly created Form with buttons.

---

Initial post:

Please take a look at this post for reference: Excel VBA create a button beside cell.

• Possible error due to (object doesn't support property or method), is within the Caption property. As it has to be set with theBttn.Object.Caption

Try this please:

With Butn
  .Name = "CommandButton" & i
  .Object.Caption = i
  .Width = 100
  .Left = 300
  .Top = 10 * i * 2
End With