CODEWITHSUNDEEP
excelvbaeventscomboboxactivex
christian890
christian890

Reputation: 147

Dynamic ActiveX.ComboBox Change Event

i got a Problem while working on a little project. I hope someone can help me, thanks in advance!

What i am trying to accomplish?

I create dynamic new ActiveX.ComboBoxes with a given List in it. When the user selects the ComboBox and changes the selected Item in the List i need an event to trigger. For every dynamic comboboxes the event should be the same. Example: Combobox in D10 changes from "ItemA" to "ItemB" then i need an Event that checks in a Table for "ItemB" (Column A) and then pick the Value of the same Row of Column B and put it in the Column E10 where the combobox is located.

I have no clue how to add DynamicEvents and then let them act all the same. And even if i still need to delete events dynamic too since the user can delete Rows and Comboboxes again.

What i tried:

Linking the Combobox to its Cell below it and then i tried to get the "worksheet Change" Event working, but apparently it doesnt work with linkedcells....

Current Code:

 Sub NewComboBox(ByVal Row As Integer, Name As String, CellWidth As Integer,  CellHeight As Integer)

    Set ourCombo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                Link:=True, DisplayAsIcon:=False, Left:=plan.Cells(Row, CellNumber_Product).Left, Top:=plan.Cells(Row, CellNumber_Product).Top, Width:=CellWidth, _
                Height:=CellHeight)
        With ourCombo
            .LinkedCell = plan.Cells(Row, CellNumber_Product).Address          
            .ListFillRange = "DTB_1!A:A"

End Sub

Upvotes: 0

Views: 991

Answers (1)

Lee Li Fong
Lee Li Fong

Reputation: 259

Private Sub ComboBox1_Change()

    CB1=ComboBox1.Value
    R=WorksheetFunction.Match(CB1, Columns("A:A"),0)

    Activesheet.OLEObjects("ComBox2").ListFillRange =Cells(R,2).Address

End Sub

Upvotes: 1

Related Questions