Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VBA - open a DataValidation dropdown

Posted on 2016-11-22
6
Medium Priority
?
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 41897738
The keyboard shortcut for opening a dropdown is Alt + Down arrow.

SendKeys ("%{Down}")
0
 

Author Comment

by:hindersaliva
ID: 41897788
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 33

Expert Comment

by:Rob Henson
ID: 41897793
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:hindersaliva
ID: 41897805
Does this work for you?
Find-matches-and-freeze-selection.xlsm
0
 
LVL 23

Accepted Solution

by:
Ejgil Hedegaard earned 1000 total points
ID: 41898138
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
ID: 41909030
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

715 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