Solved

Excel VBA - open a DataValidation dropdown

Posted on 2016-11-22
6
45 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 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 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 32

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

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

Accepted Solution

by:
Ejgil Hedegaard earned 250 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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 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