?
Solved

VBA Excel form using drop down list to populate list values while displaying descriptions

Posted on 2009-04-07
7
Medium Priority
?
2,661 Views
Last Modified: 2012-06-22
I have a basic excel form that uses a drop down list of values from a worksheet and it appears to be working okay but I would like to be able to include the descriptions of the values on the list. Example: below is the drop down list values and descriptions of positions. I'd like to be able to have the list show RN, Phs Coordinator, etc for the user to select but it would write the values depending on what is selected. So the user would see RN, Data Analyst, etc...but when they select RN it would write 5004. Currently it displays and writes the numeric values. I have also included an example of my current code. Thanks

5004      RN
5005      PhsCoordinator
5006      Data Analyst
5007      Sr. Director Opp

Sub Button1_Click()
Dim MyUniqueList As Variant, i As Long
    With usr_Form.cbo_pos
            .Clear ' clear the listbox content
        MyUniqueList = PosItemList(Sheet10.Range("A4:A100"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0 ' select the first item
    End With
 
Private Function PosItemList(InputRange As Range, _
    HorizontalList As Boolean) As Variant
Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
    Application.Volatile
    On Error Resume Next
    For Each cl In InputRange
        If cl.Formula <> "" Then
            cUnique.Add cl.Value, CStr(cl.Value)
        End If
    Next cl
    PosItemList = ""
    If cUnique.Count > 0 Then
        ReDim uList(1 To cUnique.Count)
        For i = 1 To cUnique.Count
            uList(i) = cUnique(i)
        Next i
        PosItemList = uList
        If Not HorizontalList Then
            PosItemList = _
                Application.WorksheetFunction.Transpose(PosItemList)
        End If
    End If
    On Error GoTo 0
End Function

Open in new window

0
Comment
Question by:cbads
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24091077
When you say write, I'm assuming that you are taking the value from the user form combobox and storing it in a cell on the excel workbook?  My suggestion would be populate the user form with the english description of the position, store that on the worksheet, then have a vlookup setup that will retrieve the position value from a vlookup table.  If you are interested in this solution, let me know and I should be able to walk you through the vlookup setup.
HTH
Cal
0
 

Author Comment

by:cbads
ID: 24091297
Yes, I am writing the value into the worksheet. I currently have a worksheet that has cell A as the value and B as the description of the positions. I would like to try your suggestion regarding the vlookup. Thanks
cb
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24091453
Actually I was just thinking about the userform combo box, and there is a way to store multiple columns of data in it, and select based on the first value, but store the second value?  This might be easier for your to work with?  You will need to set your columnscount property to 2, and set bound column to 2(Which will cause it to return the value of the second column).  This will allow you store the name, and code, but then only return the code?
Will this work better?
I also have concerns with the vlookup, cause you may be populating more than one row of data?  With knowing the structure of the storage sheet, I can't determine which way would be best for you.
Cal
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cbads
ID: 24099079
I set the column property and bound to 2 on the combo box and changed the range in my code but it's not displaying the descritption and value side by side but instead vertically. Also, when I click save...it only enters the value if value is selected or the description if description is selected. Here's the range I changed vba code. Thanks.
cb

Dim MyUniqueList As Variant, i As Long
    With cbo_pos
        .Clear ' clear the listbox content
        MyUniqueList = PosItemList(Sheet10.Range("A4:A100", "B4:B100"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0 ' select the first item
    End With
End Sub
0
 
LVL 16

Accepted Solution

by:
Calvin Brine earned 1000 total points
ID: 24099882
From looking at your code, it looks like you are defining the combobox values from your spreadsheet.  If that is the case, then this is very easy.
1.  Add your combobox.
2.  Open the properties.
3.  Change BoundColumn to 2.
4.  Change ColumnCount to 2.(Position Title MUST b the first column and position code the 2nd on your spreadsheet)
5.  Change rowsource to A4:B100(Or whatever range your positions are stored in)
6.  Change ControlSource to the cell you want to store the select value data in.  SInce bound column is 2, you should get the second column of the combobox selection.
That should be it.
 
I've also whipped up some simple code to show how to load multi column data into a multi column combobox.  Looks like the data needs to be stored in an array and then loaded to the combobox.
HTH
Cal

Combobox.xls
0
 

Author Comment

by:cbads
ID: 24116137
Thanks for the code. I'm unsure what you mean about "Change ControlSource to the cell you want to store the select value data in". The combo box is cbo_pos.value...is this where i add the controlSource? Could you give me an axample? Thanks

cb

Private Sub cmdOK_Click()
    ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
    ActiveCell.Offset(0, 1) = txt_fname.Value
    ActiveCell.Offset(0, 2) = txt_lname.Value
    ActiveCell.Offset(0, 3) = cbo_source1.Value
    ActiveCell.Offset(0, 4) = cbo_pos.Value
           
   Range("A2").Select
   usr_form.txt_fname.SetFocus
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 24137529
If you are using an ActiveX combo box(From the Controls Toolbar), you just need to right click on the control and select properties.  One of the properties will be the ControlSOurce.  You can link this to a specfic cell, and store the selected value in this cell.
HTH
Cal
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question