How to select a certain item in a drop-down list using VBA?

jkalpus

New Member
Joined
Apr 17, 2004
Messages
46
If I select a certain item in a drop-down list, is there a way to populate a certain item in another cell that has a drop-down list?For instance, let's say I have a drop down list in A1 which lists RED, GREEN, BLUE. If I select RED in A1, if B1 has a drop-down list which lists 1,2,3, can I have 1 automatically selected in B2 when RED is selected in A1? Many thanks in advance ...
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
When you select RED in A1, you want 1 entered via VBA in cell B1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Cells(1).Address = "$A$1" Then
        If Target.Cells(1).Value2 = "RED" Then
            Application.EnableEvents = False
            Me.Range("B1").Value2 = 1
            Application.EnableEvents = True
        End If
    End If


End Sub
 

jkalpus

New Member
Joined
Apr 17, 2004
Messages
46
When you select RED in A1, you want 1 entered via VBA in cell B1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Cells(1).Address = "$A$1" Then
        If Target.Cells(1).Value2 = "RED" Then
            Application.EnableEvents = False
            Me.Range("B1").Value2 = 1
            Application.EnableEvents = True
        End If
    End If


End Sub


Thanks, Fazza, for your speedy reply!
Not, exactly, what I was looking for.

I need a selection in one drop-down list, say "RED", to populate with let's say the FIRST selection (whatever that may be) in another drop-down list.

For example, if A1 has a drop-down list of RED, GREEN, BLUE, when I select RED in A1, I want that action to select the FIRST (or SECOND, or THIRD) element in a drop-down list in B1. So, if I change the values in the drop-down list in B1, I want that action to select the NEW first (or SECOND or THIRD, etc.) element in that drop-down list. I hope this clarifies what I would like.

Thank you a LOT in advance! jk
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Yes, I understand this question too.

Suggest you set up defined names for the input lists to the data validation - such as tblCOLORS & tblORDINALS - then some INDEX/MATCH, or if they are adjacent VLOOKUP, formulas. Whatever works, it should be straightforward enough.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,133
Messages
5,599,909
Members
414,345
Latest member
Jonathan43

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top