2

I am very new to excel programming and VBA. I am stuck at a point where I have random number of dynamically created combo boxes (ComboBox1, ComboBox2.... ComboBoxN). I need to implement a functionality where if I select a value in the ComboBox[i] (where i can be any random number between 1 to N), then it should trigger an event that will populate values in ComboBox[i+1].

How do I write a Sub for this? Is there any other way to implement this if not in a Sub?

Vikram
  • 65
  • 2
  • 9
  • Are the comboboxes on a worksheet or a userform? –  Sep 18 '16 at 08:20
  • 1
    http://stackoverflow.com/questions/10224511/assign-code-to-a-button-created-dynamically – user2226755 Sep 18 '16 at 08:29
  • ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _ Link:=False, DisplayAsIcon:=False, Left:=50, Top:=TopD, Width:=100, _ Height:=15) @ThomasInzina – Vikram Sep 18 '16 at 08:54

2 Answers2

3

In order to create a group events you'll need a custom class to capture the events ( ObjectListener ), public variable to keep the class references alive (usually a collection or array - ComboListener ) and a Macro to fill the collection ( AddListeners_ComboBoxes ).

Call the AddListeners_ComboBoxes Macro from the Workbook_Open(). You will need call AddListeners_ComboBoxes again if the code breaks.

Standard Module

Public ComboListener As Collection

Sub AddListeners_ComboBoxes()
    Dim ws As Worksheet
    Dim obj As OLEObject
    Dim listener As ObjectListener

    Set ComboListener = New Collection

    For Each ws In Worksheets
        For Each obj In ws.OLEObjects
            Select Case TypeName(obj.Object)
            Case "ComboBox"
                Set listener = New ObjectListener
                Set listener.Combo = obj.Object

                ComboListener.Add listener
            End Select
        Next
    Next
End Sub

enter image description here

Class ObjectListener

Option Explicit

Public WithEvents Combo As MSForms.ComboBox

Private Sub Combo_Change()
    MsgBox Combo.Name
    Select Case Combo.Name
        Case "ComboBox2"
        ActiveSheet.OLEObjects("ComboBox3").Object.ListIndex = 1

    End Select

End Sub
0

As an alternative to the "Class" approach shown by Thomas Inzina here's a "less structured" approach:

Private Sub ComboBox1_Change()
    PopulateCombo 2
End Sub

Private Sub ComboBox2_Change()
    PopulateCombo 3
End Sub

Private Sub ComboBox3_Change()
    PopulateCombo 4
End Sub

Private Sub ComboBox4_Change()
    PopulateCombo 1 '<--| will "last" combobox populate the "first" one?
End Sub

Private Sub PopulateCombo(cbNr As Long)
    With ActiveSheet.OLEObjects("ComboBox" & cbNr) '<--| reference the combobox as per the passed number
        .ListFillRange = "Sheet1!J1:J10" '<--| populate it with "Sheet1" worksheet range "A1:A10"
        .Object.ListIndex = 1 '<--| select its first item
    End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28