Event Handler for Dynamically Created Combobox

[jcornish]

Hi All,

I have a combobox named bfPoject2 that is created programmatically as follows:

Set bfProject2 = Controls.Add("Forms.ComboBox.1", bfProject2)

    With bfProject2
        .Top = 195
        .Left = 474
        .Width = 180
        .List = Range(Cells(2, 4), Cells(lastRowProj, 4)).Value
        .Font.Bold = False
        
    End With

When the user selects an item from the combobox it then populates a second combobox with a list of items using the event handler:

Sub bfPoject2_Change()

Dim keyValue As Integer

    If bfWkPkge2.ListCount <> 0 Then
    
        For k = 0 To bfWkPkge2.ListCount - 1
     
                bfWkPkge2.RemoveItem 0
                    
        Next k
        
    End If

    For i = 2 To lastRowProj
    
        If bfPoject2.ListIndex = Cells(i, 1).Value Then
        
            lastRowWkPack = ActiveWorkbook.Worksheets("Library").Cells(Rows.Count, 5).End(xlUp).Row
            keyValue = Cells(i, 1).Value
            
                For j = 2 To lastRowWkPack
                
                    If Cells(j, 5).Value = keyValue Then
                    
                        bfWkPkge2.AddItem Cells(j, 8).Value
                        
                    End If
                    
                Next j
            
        End If
        
    Next i


End Sub

However, for some reason the event is not triggering the event handler! I use very similar code for bfPoject1 and bfWkPkge1 that is not added programmatically and it works fine.

All and any help appreciated!

 

[Norie]

Controls created dynamically will not trigger existing event code.

Are you going to be adding more than one combobox that would trigger the same/similar code?

 

[jcornish]

Hi Noire,

Thank you for your quick reply!

Yes, the code is for a time booking form. The user clicks a button to add a new line that consists of 8 textboxes: Sat - Fri hours, hours total and 2 comboboxes for the code to be assigned too.

See picture: Pasteboard — Uploaded Image

This event handler needs to be triggered each time the project combobox is changed for each row.

As an aside, I have hard coded the addition of 9 rows. I pretty sure there must be a smarter way of doing this. I have read about classes, but have struggled with how to implement them in the context of this.

 

[Norie]

if you kept the current set up, you could use a class to handle the events of the controls you are adding.

However, at might not fit your needs, you could take another approach.

What I'm thinking is that you have 1 row with the textboxes and comboboxes, and whatever else added at design time.

You woud also have a command button and a multicolumn listbox.

The user enters data in the textboxes/comboboxes etc, presses the command button, the data is added to the listbox and the textboxes/comboboxes are cleared for the next entry.

Is that anything you could work with?

 

[jcornish]

I see what you are getting at. One issue though is that users are likely to want to edit the timecard as they go. For example they might find they worked at total of 40.5 hours when they were aiming for 40.

If you could start me off with some help on using a class to handle the events for the dynamically added stuff.

Is it possible to use a class to add as many lines of comboboxes and textboxes as a user needs? It would be great not to be limited to 9, but still have the flexibility to edit them all.

Thanks for your help so far!

 

[Norie]

Buttons could be added to allow the user to edit/delete etc any existing data in the listbox.

As for the class adding the lines, that's not how it would work.

The class would only be used to handle the events of the controls you are adding and you every time you added a new 'line' you would need to associate the controls with the appropriate class.

Though I've worked with dynamically created controls and classes I've never actually done it with this sort of thing where the buttons are added via the click of a button.

Should be straightforward though, I'll see if I can come up with something.

 

[Norie]

I've mocked up a very simple example, you can download it here UFDynamicComboEx.xls.

 

[Kyle123]

Bit rough around the edges, but you should get the idea:
Class cRow

Private textBoxes As Collection
Public WithEvents projectCombo As MSForms.ComboBox
Public WithEvents WorkpackageCombo As MSForms.ComboBox
Public sumLabel As MSForms.Label

Public WithEvents parent As UserForm

Private Const START_LEFT As Long = 5
Private Const START_TOP As Long = 50
Private Const ROW_PADDING As Long = 20

Public rowIndex As Long

Private Enum textbox_properties
    Height = 18
    Width = 48
    paddingRight = 10
End Enum




Public Sub TextBoxes_Calculate()

    'Add up the values of all the textboxes
    Dim tb As Object
    Dim total As Double
    For Each tb In textBoxes
        total = total + Val(tb.NumericTextbox.Value)
    Next tb
    
    sumLabel.Caption = total
    
End Sub

Private Sub Class_Initialize()

End Sub

Public Sub AddRow()
    Dim NumericTextbox As cNumericTextBox
    Dim tb As MSForms.TextBox
    
    Dim x As Long
    
    Set textBoxes = New Collection
    
    For x = 1 To 5
        
        Set tb = parent.Controls.Add("Forms.Textbox.1")
        
        With tb
            .Height = textbox_properties.Height
            .Width = textbox_properties.Width
            .Left = START_LEFT + ((textbox_properties.Width + textbox_properties.paddingRight) * x)
            .Top = (START_TOP + textbox_properties.Height + ROW_PADDING * rowIndex)
        End With
    
        Set NumericTextbox = New cNumericTextBox
        Set NumericTextbox.parent = Me
        Set NumericTextbox.NumericTextbox = tb
        
        textBoxes.Add NumericTextbox
        
    Next x
    
    Set sumLabel = parent.Controls.Add("Forms.Label.1")
    With sumLabel
        .Left = START_LEFT + ((textbox_properties.Width + textbox_properties.paddingRight) * 6)
        .Top = (START_TOP + textbox_properties.Height + ROW_PADDING * rowIndex)
    End With
    
    Set projectCombo = parent.Controls.Add("forms.combobox.1")
    With projectCombo
        .Left = START_LEFT + ((textbox_properties.Width + textbox_properties.paddingRight) * 6) + sumLabel.Width + textbox_properties.paddingRight
        .Top = (START_TOP + textbox_properties.Height + ROW_PADDING * rowIndex)
        .Width = 200
    End With
    
    Set WorkpackageCombo = parent.Controls.Add("forms.combobox.1")
    With WorkpackageCombo
        .Left = START_LEFT + ((textbox_properties.Width + textbox_properties.paddingRight) * 6) + sumLabel.Width + textbox_properties.paddingRight + projectCombo.Width + textbox_properties.paddingRight
        .Top = (START_TOP + textbox_properties.Height + ROW_PADDING * rowIndex)
        .Width = 200
    End With
        
    
End Sub

Public Sub Dispose()
    Dim tb As Object
    For Each tb In textBoxes
        Set tb.parent = Nothing
    Next tb
End Sub

Class cNumericTextbox

Public WithEvents NumericTextbox As MSForms.TextBox
Public parent As cRow


Private Sub numericTextbox_Change()

    With Me.NumericTextbox
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
            .Value = Left(.Value, Len(.Value) - 1)
        End If
    End With
    
    'Dummy Event to Raise
    parent.TextBoxes_Calculate
    
End Sub

Userform:

Dim dataRows As Collection


Private Sub CommandButton1_Click()

    Dim dataRow As cRow
    Set dataRow = New cRow
    
    With dataRow
        Set .parent = Me
        .rowIndex = dataRows.Count
        .AddRow
        .projectCombo.List = Array(1, 2, 3)
        .WorkpackageCombo.List = Array("a", "b", "c")
    End With
    
    dataRows.Add dataRow

End Sub

Private Sub UserForm_Initialize()

    Set dataRows = New Collection
    CommandButton1_Click
    
End Sub



Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Dim dr As Object
    For Each dr In dataRows
        dr.Dispose
    Next dr
End Sub

 

[Norie]

Nice one Kyle, got to remember that classes aren't limited to one control/event/whatever.

 

[jcornish]

Wow! Thank you Kyle.

Wish I had checked this page this morning. I managed to get the rows added dynamically myself, but have been struggling with the event handlers. I pretty sure example will help me crack it. Will post the final code for reference.

Thank you to you both!