VBA (Excel) - Get the Active Object (ActiveX ComboBox) during Runtime

375 Views Asked by At

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 i got an Event triggered. (this works just fine).

Now: I need a methode to check which "ActiveX ComboBox" triggered the Event. Is there any way to check the "ActiveObject" like i can do with "ActiveCell" ? The Problem is ActiveCell does NOT work since the Cell isnt active just the object above the cell.

Current Code: Adding new ComboBoxes

 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

How i get the Event triggered:

I used a dirty way to do it

I linked the Combobox to the cell below it. Then i Wrote a Formular in another cell that it should be the same value. And then i work with the "calculate" Event (which sadly doesnt provide the target) for example:

Combobox1 = above Cell A2
Combobox1.linkedcell = A2
B2 = A2

now if i Change the Combobox Item, the Value of A2 changes and with it the Value of B2 and the Event get triggered.

0

There are 0 best solutions below