Solved

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

Posted on 2009-04-07
7
2,627 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

867 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

21 Experts available now in Live!

Get 1:1 Help Now