Can't expand dropdown with only one click 2

[tbarthen]

Is there a way in Excel to make Data Validation dropdowns actually dropdown on the first click? You know, as it currently works, you have to click on the cell with the data validation to select it. Then you have to click on it again to make the dropdown expand.

Is there some kind of VB code to make this expand in a single click? I figure the "Worksheet_Change" sub would be the place to do this in, but how do you do it? Is it even possible?

If it isn't possible, I guess an acceptable alternative would be for the cell to autocomplete if you begin typing one of the values with the cell is linked to. I don't know how to do that either though. The only way I know of that autocomplete will work is if the values you are typing already exist in the column you're typing in.

Thanks for your help if anyone comes up with any ideas.

 

[Mike555]

You should be able to create a GotFocus event as follows:

Me!ComboBoxName.Dropdown

--
Mike

In defeat: unbeatable. In victory: unbearable. -- Winston Churchill

 

[tbarthen]

Hmmm.....I'm not sure that's gonna work Mike. I didn't actually create a combobox. Its my understanding that when you use data validation, it makes a cell work as a dropdown, but its still just a cell. Its not suddenly a combobox.
So basically, when you click on the cell that has the data validation, the Worksheet_SelectionChange or Worksheet_Change event will catch it, and you can refer to the active cell by using "Target." There is no such thing as Target.Dropdown though.

Let me know if I'm missing something here.

Thanks

 

[Zathras]

If you grok VBA you can trap the SelectionChange event. If the selected cell is one that you want to auto-drop, send the Alt-Down keystroke to it:
[blue]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Address = "$D$4" Then
    SendKeys ("%{DOWN}")
  End If
End Sub

[/color]
Right-click on the worksheet tab and select "View Code" to get started.

 

[xlbo]

Z - beautiful - I was trying to think up something for this. You can have a star from me for starters

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[tbarthen]

Wow! It sounds so simple now that you point it out, but I never would've thought of it myself.

Thanks A LOT Zathras!