Selecting a specific item from Dropdown in excel v

问题:

I'm working out a dashboard for my office. It all works, but I wanted to add in an option that instead of searching through 250+ items in a dropdown, you could also click on a cell and the dropdown would change to that value, and the assigned macro would run for that dropdown. So far I can't figure out how to have vba select a specific item from a dropdown. I can have the text change, but that doesn't select the index of the dropdown.

Any suggestions?

回答1:

If you are using an ActiveX drop down list, (a combobox) this is how you would change the selected value in the drop down list:

ComboBox1.Value = "New Value"

If you are using data validation as a drop down list, then you just need to change the cell value as you would change any other cell:

'assuming the drop down list (data validation) is in Cell(1, 1)
cells(1,1) = "New Value"

Also you could check this article I've written on my blog about working with drop down lists in VBA Excel VBA Drop Down Lists

回答2:

I wanted to do the same: Select an option from several drop down lists, then "reset" all of them to a starting point. Recording a macro and change the drop down list wont work.

However, I did the following:

• Start recording macro
• I typed all the values I wanted to be the "starting point once reset on the drop down list
• stop macro.

It worked. Now I hit the macro and I get all the values "reset". Cheap trick, I know but worked for me.