Create Click events for Command buttons created dynamically at Runtime

[jacko2401]

Hi

I have a userform that has one combobox at the top created manually. When the userform is opened, the user select an option in the combobox (these options are taken from a range on 1 worksheet). From the selection of the combobox, I use the comboxbox's change event to create and display 5 columns of textboxes and 2 columns of command buttons on the userform.

The number of rows of textboxes created depend on the option selected from the combobox since each option links to a different range of cells. Each of the 5 textboxes in each are set to be ".enabled = False" and display text as per the cell values within a range on another worksheet. 2 Columns of command buttons are created at the end of each row of textboxes - 1 is enabled and the other is not.

The creation of the textboxes and command buttons works as required. However, I am having problems with setting click events for each command buttons. When the 1st column of Command buttons are created, I need the click events to be created and filled out with 2 actions:

1. Enable all textboxes in the same row as the command button
2. Enable the other command button in the same row.

Here is the code I have so far that creates the textboxes and command buttons.

Each of the 5 textboxes and 2 command buttons have a unique name so the 1st row will have textbox and command button names of cTxtA1, cTxtB1, cTxtC1, cTxtD1, cTxtE1, CmdAmend1 and CmdConfirm1. The 2nd row will have the same names but with 2 on the end and so on. The bold sections is the code for the creation of the command buttons that I want click events for.

Any help will be much appreciated.

Steve

Private Sub CboTeamSelect_Change()

Application.ScreenUpdating = False

If CboGroupSelect.Value = "" Then Exit Sub

Dim cTxtA As Control, cTxtB As Control, cTxtC As Control, cTxtD As Control, cTxtE As Control
Dim CmdAmend As Control, CmdConfirm As Control
Dim iNum As Integer
Dim TxtTop As Long
Dim RngGroup As Range 'Find the range of cells depending on the group selected
Dim LngLastGroup As Long 'Find the last row with RngGroup range
Dim Group As Range 'Used for each cell in RngGroup

LngLastFixture = Sheets(CboGroupSelect.Value).Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set RngGroup = Sheets(CboGroupSelect.Value).Range("A3:A" & LngLastGroup)
 
TxtTop = 75
iNum = 1

For Each Group In RngGroup 'go through each cell in the range
 
Set cTxtA = Me.Controls.Add("Forms.TextBox.1", "TxtA" & iNum, True)
With cTxtA
    .Width = 50
    .Height = 20
    .Top = TxtTop
    .Left = 10
    .Enabled = False
    .Value = Group.Value
End With[INDENT]

Set cTxtB = Me.Controls.Add("Forms.TextBox.1", "TxtB" & iNum, True)
With cTxtB
    .Width = 50
    .Height = 20
    .Top = TxtTop
    .Left = 10
    .Enabled = False
    .Value = Group.Value
End With

Set cTxtC = Me.Controls.Add("Forms.TextBox.1", "TxtC" & iNum, True)
With cTxtC
    .Width = 50
    .Height = 20
    .Top = TxtTop
    .Left = 10
    .Enabled = False
    .Value = Group.Value
End With

Set cTxtD = Me.Controls.Add("Forms.TextBox.1", "TxtD" & iNum, True)
With cTxtD
    .Width = 50
    .Height = 20
    .Top = TxtTop
    .Left = 10
    .Enabled = False
    .Value = Group.Value
End With

Set cTxtE = Me.Controls.Add("Forms.TextBox.1", "TxtE" & iNum, True)
With cTxtE
    .Width = 50
    .Height = 20
    .Top = TxtTop
    .Left = 10
    .Enabled = False
    .Value = Group.Value
End With
[/INDENT]
[B]Set CmdAmend = Me.Controls.Add("Forms.CommandButton.1", "CmdAmend" & iNum, True)
With CmdAmend
    .Width = 70
    .Height = 20
    .Top = TxtTop
    .Left = 420
    .Enabled = True
End With[/B]
 
[B]Set CmdConfirm = Me.Controls.Add("Forms.CommandButton.1", "CmdConfirm" & iNum, True)
With CmdConfirm
    .Width = 70
    .Height = 20
    .Top = TxtTop
    .Left = 420
    .Enabled = False
End With
[/B]
Next Group

 

[sijpie]

If the table is static on the form with just the number of rows changing & the buttons for each row, then I would create al lthe buttons and their click codes in advance and just have the property visible set to false, until required depending on the number of rows you show. that way you are not creating anything on the fly just making things visible or not.
You could do the same with the table if required. Is that an option?

 

[sijpie]

I use this approach quite often, and will also change the size of the userform to avoid large empty spaces where the rows & controls are hidden