Solved

Excel VBA - open a DataValidation dropdown

Posted on 2016-11-22
6
16 Views
Last Modified: 2016-12-01
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
Comment
Question by:hindersaliva
  • 3
  • 2
6 Comments
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
Comment Utility
The keyboard shortcut for opening a dropdown is Alt + Down arrow.

SendKeys ("%{Down}")
0
 

Author Comment

by:hindersaliva
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:hindersaliva
Comment Utility
Does this work for you?
Find-matches-and-freeze-selection.xlsm
0
 
LVL 20

Accepted Solution

by:
Ejgil Hedegaard earned 250 total points
Comment Utility
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
 

Author Comment

by:hindersaliva
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now