ComboBox DropDown list keeps adding data

Dear All,

I hope you are all well.

I have a ComboBox in a UserForm with names of employees. That RowSource is from a named range called "lstNames" (updated in "Data" sheet).

I have the following code that hides the "Empty" cells from the Named Range:

Please Login or Register  to view this content.

This code works perfectly as it only shows the names of employees and hides the "Empty" ones as mentioned above.

The only problem is, if you close and open again the dropdown menu, it keeps adding the same list of employees.

I have attached an example on this thread.

I tried to add the following code that stops adding the repeated names, but I am unable to select any name from the list:

Please Login or Register  to view this content.

Any ideas on how to solve this? Your assistance will be highly appreciated.

Best regards,
Filipe

Good evening pipoliviera

You've attached this macro to the DropButtonClick event, meaning it's fired every time a user clicks the combobox.
Either run it on userform initialise, so it only runs once, or if you really need to run it under DropButtonClick, use ComboBox1.Clear first to empty it, then let it populate.

HTH

DominicB

Hi Dominic,

Thanks for your reply.

I need to run it under the DropButtonClick event because on my real workbook, the dropdown list changes depending on a previous ComboBox selection.

As mentioned on the thread, if I add ComboBox1.Clear at the beginning of the code, I am unable to select anything from the dropdown menu.

I have a test file attached if you want to try it yourself.

Thanks a million in advance,
Filipe

Hi pipliveira

Course it does. Should of thought of that!

Try pegging it to a different event? I tried Private Sub ComboBox1_Enter() and that seemed to work OK.
Alternatively, populate combobox1 from the previous combobox, say the Exit event?

HTH

DominicB

Yep, so simple...

Thanks a million Dominic, I am really appreciated for your help.

I thought the drop-down list should be set on the DropDown event and nowhere else.

Once again, thanks a million,
Filipe