0

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.

christian890
  • 147
  • 1
  • 12
  • How does your event get triggerd? I imagine you want to append that code, so please share that with us. – Luuklag May 11 '18 at 07:43
  • I edited my Post with it. Thank you for your time! – christian890 May 11 '18 at 07:49
  • 1
    That is indeed a dirty way. Have you looked at the Worksheet_Change event already? Using an intersect approach on that to set scenario's for what to happen when a certain cell is changed. If you provide every combobox with its own unique linked cell, then it is easily determinable which combobox fired the event. – Luuklag May 11 '18 at 07:51
  • 1
    This is gona be a bit vague but this is the method I've used in the past: when you add a dynamic object, you can assign a UDF to it (this is the bit I don't remember the syntax of). Everytime an interaction happens with this new object, the UDF is called. This UDF can then save the name of your object (maybe in a global variable). This way you can always access the last used object. If I find how I did that, I will post it here – Zac May 11 '18 at 08:01
  • @Luuklag yes the Problem is the Woksheet_Change Event does NOT trigger if a value of a linkedcell or a value is changed by a Formular. -Zac thanks i appreciate it! – christian890 May 11 '18 at 08:20
  • 1
    You can also assign the macro to the combobox, see here: https://stackoverflow.com/questions/26479840/assigning-macros-to-a-combobox – Luuklag May 11 '18 at 08:22
  • Yeah i tried that by Adding Events to the Objects i create dynamically and then link them all to the same macro. I used https://stackoverflow.com/questions/3014421/how-to-add-events-to-controls-created-at-runtime-in-excel-with-vba?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa but i in the new class i Need to set the ObjectType and I can only choose MsForms.ComboBox but i Need the ActiveX combobox1 and i dont know how to choose that... But adding Events to the new comboboxes would do it too yeah (the Problem with your link is i Need to do it programmatically) – christian890 May 11 '18 at 08:33
  • ActiveX controls are OLEO objects, not MsForms objects, see: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/using-activex-controls-on-sheets – Luuklag May 11 '18 at 09:15
  • yeah but when i say "Public WithEvents butEvents As OLEObjects.Combobox1 it doesnt work – christian890 May 11 '18 at 09:24

0 Answers0