Interesting Use of Combo-Box Form Control

I work in the HR department of a company. I would like to have a drop-down with many choices in cell A1. Let’s say I have about 10 different leadership traits as choices in cell A1. Once a choice in cell A1 is selected I want the cell B1 to be automatically populated with the relevant trait description. I have heard that this can this be done in MS Excel. I have tried the data validation route but it doesn’t seem to work. Also I wouldn’t like to work with Excel VBA at present. Would appreciate quick help.

The complete process is described below:

Write Trait and Description as headers is sheet Traits in cells A1 and B1.
In cells A2:A11 enter the traits.
In cells B2 to B11 enter the descriptions
Select the complete range of cells from A1 to B11
Click on ‘Formulas’
Under the group ‘Defined Names’ select create from selection
From the pop-up window keep only the check box against ‘Top Row’. Remove any other selected check box.
Click OK
Go to sheet Display
Click on the developer tab
Under the controls group click on insert
Select combobox from the form controls
Click and drag a suitable size of the combobox on your sheet
Right click on the control
Select Format Control…
In the new pop-up window select ‘Control’
in the Input range text-box type ‘Trait’
In ‘Cell Link’ text-box type Traits!C2
In the text-box against the ‘drop down line’ you can type 10 if you wish to show all the traits
Now in the sheet Display in cell B2 or any appropriate cell enter the formula ‘=INDEX(Description,Traits!C2)’
This formula now uses the index in cell C2 to get the correct data from the ‘Description’ named range.

Watch the Excel training video below about using a combo-box form control to display data automatically:

2 thoughts on “Interesting Use of Combo-Box Form Control

  1. How we can use index function with combo box(active x control) to pull data from multiple columns in excel 2010

  2. I am having Data in Excel sheet “City” i want search City base on Textbox and result will be in Listbox. My code are working but search only one time not second time put command button. if excel restart then again search. code as follow:
    Private Sub cmdSearchCity_Click()
    ‘Search City from Sheet City base on Textbox (tbSCity) put value in ListBox (lbCity)
    Dim sh As Worksheet
    Set sh = Sheets(“City”)
    Dim i As Long, x As Long, p As Long, l As Long
    l = sh.Range(“A” & Rows.Count).End(xlUp).Row ‘Last Row
    For i = 2 To l
    For x = 1 To Len(sh.Cells(i, 1))
    p = Me.tbSCity.TextLength
    If LCase(Mid(sh.Cells(i, 1), x, p)) = Me.tbSCity And Me.tbSCity “” Then
    With Me.lbCity
    .AddItem sh.Cells(i, 1) ‘City
    .List(lbCity.ListCount – 1, 1) = sh.Cells(i, 2) ‘Sate
    .List(lbCity.ListCount – 1, 2) = sh.Cells(i, 3) ‘Country
    .List(lbCity.ListCount – 1, 3) = sh.Cells(i, 4) ‘Longitude
    .List(lbCity.ListCount – 1, 4) = sh.Cells(i, 5) ‘Latitude
    .List(lbCity.ListCount – 1, 5) = sh.Cells(i, 6) ‘TZone
    End With
    End If
    Next x
    If i = l Then
    Exit For
    End If
    Next i
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.