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
– Paresh J, Commented Apr 2, 2015 at 5:53

user1274820 · Accepted Answer · 2015-04-02 06:04:50Z

2

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

answered Apr 2, 2015 at 6:04

user1274820

8,1894 gold badges39 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Afonso Medeiros · Accepted Answer · 2022-09-15 20:43:21Z

0

If your NumLock button is being turned off, use the SendKeys twice with DoEvents.

    Application.SendKeys ("%{UP}")
    DoEvents
    Application.SendKeys ("%{UP}")

Worked with me. :)

edited Sep 15, 2022 at 20:43

answered Sep 15, 2022 at 20:26

Afonso Medeiros

671 gold badge3 silver badges10 bronze badges

Collectives™ on Stack Overflow

Make the list drop down using VBA (data validation)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related