none
Connect 2 drop down menu's RRS feed

  • Question

  • I have made 2 drop down menu´s with different value´s.  The problem is that i need to connect the 2 menu´s. They are made with data validation.

    The first drop down menu contains the value´s: A,B,C,X

    The second drop down menu contains the value's: 1,2,3,4

    A connects to 1

    B connects to 2

    C connects to 3

    X connects to 4

    When a change is made in one of the menu's, the other menu has to change also.

    How can this be done?

    Thursday, November 16, 2017 3:36 PM

Answers

  • Hi JterH,

    I can see that you have 2 drop down and you want to select "1" in second combo box if you select "A" in first combo box and so on and vice versa.

    so you can try to use "ListIndex" property of combobox.

    example:

    first assign the value to both combobox.

    Private Sub CommandButton1_Click()
    Me.ComboBox1.AddItem ("A")
    Me.ComboBox1.AddItem ("B")
    Me.ComboBox1.AddItem ("C")
    Me.ComboBox1.AddItem ("D")
    Me.ComboBox1.AddItem ("X")
    Me.ComboBox2.AddItem ("1")
    Me.ComboBox2.AddItem ("2")
    Me.ComboBox2.AddItem ("3")
    Me.ComboBox2.AddItem ("4")
    Me.ComboBox2.AddItem ("5")
    
    End Sub

    then you can use "ComboBox_Change" event of Combo box1 & Combo box2. like below.

    Private Sub ComboBox1_Change()
    Me.ComboBox2.ListIndex = Me.ComboBox1.ListIndex
    End Sub
    Private Sub ComboBox2_Change()
    Me.ComboBox1.ListIndex = Me.ComboBox2.ListIndex
    End Sub

    Output:

    so with just one line of code, you can fulfil your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 17, 2017 7:03 AM
    Moderator

All replies

  • If the value of the second dropdown depends totally on the value of the first one, you don't need the second dropdown; you could use a formula instead. Let's say the first dropdown is in cell A1. The formula could be

    =MATCH(A1,{"A","B","C","X"},0)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 16, 2017 3:54 PM
  • Hi JterH,

    I can see that you have 2 drop down and you want to select "1" in second combo box if you select "A" in first combo box and so on and vice versa.

    so you can try to use "ListIndex" property of combobox.

    example:

    first assign the value to both combobox.

    Private Sub CommandButton1_Click()
    Me.ComboBox1.AddItem ("A")
    Me.ComboBox1.AddItem ("B")
    Me.ComboBox1.AddItem ("C")
    Me.ComboBox1.AddItem ("D")
    Me.ComboBox1.AddItem ("X")
    Me.ComboBox2.AddItem ("1")
    Me.ComboBox2.AddItem ("2")
    Me.ComboBox2.AddItem ("3")
    Me.ComboBox2.AddItem ("4")
    Me.ComboBox2.AddItem ("5")
    
    End Sub

    then you can use "ComboBox_Change" event of Combo box1 & Combo box2. like below.

    Private Sub ComboBox1_Change()
    Me.ComboBox2.ListIndex = Me.ComboBox1.ListIndex
    End Sub
    Private Sub ComboBox2_Change()
    Me.ComboBox1.ListIndex = Me.ComboBox2.ListIndex
    End Sub

    Output:

    so with just one line of code, you can fulfil your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, November 17, 2017 7:03 AM
    Moderator
  • This is exactly what i was looking for!

    Thnx for your help.

    Friday, November 17, 2017 10:29 AM