Solved

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

Posted on 2009-04-07
7
2,623 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 250 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now