Identifying last selected ComboBox

[otrava18]

Hello everyone,

I have a userform with 20 Comboboxes and I need your advice to identify which ComboBox it was selected last time. If users wants to use this shortcut they will use a doubleclick on Combobox because the single click it needs also to enter a value. I put on doubleclick action this code:

Private Sub ComboBox5_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Range("E1").Value = "ComboBox5"
End Sub

Every ComboBox will have a code like this. After this, i need to use this value to autocomplete, with a command button, a value but i don`t know how to declare this variables.

Private Sub CommandButton17_Click()
Dim X As Integer
X = Range("E1").Value
X.Value = "1"

I don`t know what is wrong here, can you help me with an advice ? It will be great if you have also another solution for this.

Thank you for your help !

 

[GWteB]

but i want to have something there.

Of course you want that, that is the whole purpose of a combo box.

Don't use RowSource since it limits your options.
Populate your combo box with the AddItem methode, like @mikerickson did; his code adds just an "x" , but obviously you can add more items to your combo boxes.

 

[otrava18]

Of course you want that, that is the whole purpose of a combo box.

Don't use RowSource since it limits your options.
Populate your combo box with the AddItem methode, like @mikerickson did; his code adds just an "x" , but obviously you can add more items to your combo boxes.

Ok, now if i use this, everything seems to be ok, but how can i use the "E1" data information, because there is the name of the last ComboBox ? I don`t know how to create that button.
Maybe @mikerickson or you can have any idea because i don't have one.

Thank you !

 

[GWteB]

but how can i use the "E1" data information, because there is the name of the last ComboBox ?

You don't need to store the name in a worksheet cell, because you have stored the last clicked combo box as an object (type MSForms.ComboBox) in the LastDoubleClickedComboBox variable.
So to obtain its name use the statement:

LastDoubleClickedComboBox.Name

 

[mikerickson]

Hello Mike,

I think i miss somthing, because for me is not working. I started a new workbook and a new userform1 but i receive an error when i put your code and i open the userform. The error is Run-time error '70': Permision denied. And debugging goes me to "oneControl.AddItem "x""

The code is using automation features that exceed what your security permissions allow. Are you working on your own computer or is this a work computer.

 

[Domenic]

Hello Mike,

I think i miss somthing, because for me is not working. I started a new workbook and a new userform1 but i receive an error when i put your code and i open the userform. The error is Run-time error '70': Permision denied. And debugging goes me to "oneControl.AddItem "x""

If you have specified the source for oneControl using the RowSource property, you'll get that error when trying to add an item using the AddItem method . . .