asked on

Excel VBA - open a DataValidation dropdown

I have a cell which gets a DataValidation list when a button is clicked. See attached.

I'd like to make the List dropdown end up opened after the DataValidation is created in the cell. I understand it requires 'Send Keys'. Can someone please show me how it's done?

Thanks
Find-matches-and-freeze-selection.xlsm

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

When I do this nothing happens (having disabled the Worksheet_Change in the example)

Sub FillTheDropdown()
    
    Range("H16").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Tom, Dick, Harry"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    'open the InCellDropdown
   
        Application.SendKeys ("%{DOWN}")

End Sub

Open in new window

Shouldn't that fill the InCell DataValidation list AND leave it 'Open'?

Rob Henson

Just that script on its own works for me, I guess it must be something to do with the fact that you have disabled Worksheet_Change event.

Try adding

DoEvents

Open in new window

in a line before the SendKeys

hindersaliva

ASKER

Does this work for you?
Find-matches-and-freeze-selection.xlsm

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hindersaliva

ASKER

I think my problem was, the code was in a Module instead of the Sheet Module.
I have since decided not to use SendKeys as it is known to be unreliable.