[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2009-04-07
7
Medium Priority
?
2,657 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

656 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