Solved

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

Posted on 2009-04-07
7
2,639 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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.

749 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