CODEWITHSUNDEEP
excelvbavalidation
TAKL
TAKL

Reputation: 339

Make the list drop down using VBA (data validation)

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

Upvotes: 2

Views: 2868

Answers (2)

Afonso Medeiros
Afonso Medeiros

Reputation: 67

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

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

Worked with me. :)

Upvotes: 0

user1274820
user1274820

Reputation: 8144

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

Upvotes: 2

Related Questions