• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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
0
hindersaliva
Asked:
hindersaliva
  • 3
  • 2
2 Solutions
 
Rob HensonFinance AnalystCommented:
The keyboard shortcut for opening a dropdown is Alt + Down arrow.

SendKeys ("%{Down}")
0
 
hindersalivaAuthor Commented:
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'?
0
 
Rob HensonFinance AnalystCommented:
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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
hindersalivaAuthor Commented:
Does this work for you?
Find-matches-and-freeze-selection.xlsm
0
 
Ejgil HedegaardCommented:
DoEvents is not needed, but use the Selection_Change event, instead of the Change event
Then select the cell and the list is shown.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target = Range("H16") Then
        FillTheDropdown
    End If
End Sub

Open in new window

0
 
hindersalivaAuthor Commented:
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.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now