Make the list drop down using VBA (data validation)

Question

I want a cell to drop down its data validation list. I do NOT want to "create a drop-down data validation list". I already have the list, I simply want it to drop down using VBA.

Is there any way to do this?

Example: If I put data in cell A1, I want cell B1 to be selected and its data validation list should drop down. I already have the code for selecting cell B1 if cell A1 has any values, but I haven't figured out how to make the cell's list drop down.

Example code:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Sh1
        ' If row 1 in active column has a "1" in it, 
        ' we want to select the column to the right and drop down its list
        If .Cells(1, Target.Column).Value = 1 And Target.Value <> "" Then
            Target.Offset(0, 1).Select
            ' Code for dropping down the cell's list
        End If
    End With
End Sub

What exactly do you mean by "drop down its data validation list"??? Please explain with example and expected output. — Paresh J

user1274820 user1274820 · Accepted Answer · 2015-04-02T06:04:50

Select the cell and use:

Application.SendKeys ("%{UP}")

Not at my computer so I can't verify, but if you need more help look here:

http://www.mrexcel.com/forum/excel-questions/789601-excel-2010-visual-basic-applications-code-open-drop-down-list.html

Make the list drop down using VBA (data validation)

1 Answers