Dynamically created CommandButton Problem

[habman]

I have created a UserForm that adds TextBoxes and CommandButtons dynamically based on the data in my spreadsheet.

I have successfully been able to fill in the TextBoxes with the correct information.

I cannot figure how to dynamically add a "CommandButton_Click" sub to my command buttons.


Here is my code so far for my UserFrom:

Private Sub UserForm_Activate()
    On Error Resume Next
    
    Sheets("Data").Select
    LotInputs.TextBoxLotCode = Range("ZE2")
    LotInputs.TextBoxLotDate = Range("ZC2")
    
    For xRow = 2 To Range("ZK2")
     Set ctl = Me.Controls.Add("Forms.Textbox.1")  'add text box
     With ctl
      .Visible = True
      .Width = 150
      .Height = 15.75
      .Left = 6
      .Top = 102 + (xRow - 1) * 18
      .Name = "TextBoxInputLot" & xRow
      .Value = Range("ZP" & xRow)
     End With
     Set ctl = Me.Controls.Add("Forms.CommandButton.1") 'add command button
     With ctl
      .Visible = True
      .Width = 15.75
      .Height = 15.75
      .Left = 156
      .Top = 102 + (xRow - 1) * 18
      .Name = "CommandButtonInput" & xRow
      .Caption = "^"
     End With
     Me.Height = Me.Height + 18
    Next xRow

End Sub

Thanks for any help.

 

[AlphaFrog]

It doesn't look like your CommandButton is dependent on anything from the worksheet other than maybe its' position. Why not have it Pre-exist on your UserForm along with its' CommandButton_Click procedure, and then move it as needed or Show\Hide it as needed.

Is there a reason you are adding it to the user for during run-time?

Alternatively, you could have all the CommandButton_Click procedures pre-exist even if they don't have a Button on the UserForm yet.

 

[habman]

Thanks for the reply.

I query a database to get a bunch of process orders between specific date ranges.

Process orders can be "Normal" process orders or "Repack" process orders.


Anyway I build my UserForm listing all the process orders on the left and the ones that are "Repacks" I want to add a CommandButton to the right so that it can be clicked to perform a specific task on that specific process order.

I didn't show my complete IF-THEN statement on whether to show the CommandButton or not in the code above to keep things simple.

I think I need to add the button dynamically so that when the button is clicked it will grab the proper process order.


I added my IF-THEN statement to the code to show you what I am talking about.

Private Sub UserForm_Activate()
    On Error Resume Next
    
    Sheets("Data").Select
    LotInputs.TextBoxLotCode = Range("ZE2")
    LotInputs.TextBoxLotDate = Range("ZC2")
    
    For xRow = 2 To Range("ZK2")
     Set ctl = Me.Controls.Add("Forms.Textbox.1")  'add text box
     With ctl
      .Visible = True
      .Width = 150
      .Height = 15.75
      .Left = 6
      .Top = 102 + (xRow - 1) * 18
      .Name = "TextBoxInputLot" & xRow
      .Value = Range("ZP" & xRow)
     End With
     if Left(Range("ZP" & xRow),1)="R" Then
       Set ctl = Me.Controls.Add("Forms.CommandButton.1") 'add command button
       With ctl
        .Visible = True
        .Width = 15.75
        .Height = 15.75
        .Left = 156
        .Top = 102 + (xRow - 1) * 18
        .Name = "CommandButtonInput" & xRow
        .Caption = "^"
       End With
     End if
     Me.Height = Me.Height + 18
    Next xRow

End Sub

 

[Jaafar Tribak]

Maybe you can try this adaptation : (Edits in red)

[COLOR=Red][B]Private WithEvents Cmb As MSForms.CommandButton

Private Sub Cmb_Click()
    MsgBox "You clicked " & vbLf & Cmb.Name
End Sub[/B]
[/COLOR]
Private Sub UserForm_Activate()

    On Error Resume Next
    
    Sheets("Data").Select
    LotInputs.TextBoxLotCode = Range("ZE2")
    LotInputs.TextBoxLotDate = Range("ZC2")
    
    For xRow = 2 To Range("ZK2")
     Set ctl = Me.Controls.Add("Forms.Textbox.1")  'add text box
     With ctl
      .Visible = True
      .Width = 150
      .Height = 15.75
      .Left = 6
      .Top = 102 + (xRow - 1) * 18
      .Name = "TextBoxInputLot" & xRow
      .Value = Range("ZP" & xRow)
     End With
     Set ctl = Me.Controls.Add("Forms.CommandButton.1") 'add command button
     With ctl
      .Visible = True
      .Width = 15.75
      .Height = 15.75
      .Left = 156
      .Top = (xRow - 1) * 18
      .Name = "CommandButtonInput" & xRow
      .Caption = "^"
     [B][COLOR=Red] Set Cmb = ctl[/COLOR][/B]
     End With
     Me.Height = Me.Height + 18
    Next xRow

End Sub

 

[AlphaFrog]

Here's a straight-forward article on how to add a procedure dynamically.
Using the VBA Extensibility Library

If you do a web search for Excel VBA Extensibility Library, you'll find more help.