Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Dropdown list kind of like a 'select' dropdown in HTML

    I have a list of about 250 categories. I want the user to be able to choose them in a column in Excel but once they choose a category, I want the cell to display the category ID instead of the category. Is that possible? I thought about using a lookup table but I don't know how to populate my list with one of the columns (categories) and yet display the other column (categoryID) when a category is chosen. The lookup table would look something like this:
    Column A Column B
    Category A 1
    Category B 2
    Category C 3
    When the user clicks on the column in Excel, I want them to see the list of the Categories from Column A but once they click on Category B, for example, I want the cell to display 2 for the appropriate categoryID. I hope that makes sense. With HTML, you store the ID to send in the name/value pair but the user doesn't ever see it (unless it is in the query string). Here, I want the value of the ID to display...

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi jpzinn

    ..I like helping, especially if you have a z in your userID.

    So, in my attached example file, I have a data entry sheet [Main], and a sheet named [data] where I have listed the demo Department names and Department Codes.
    In my example, when you move the cellpointer, or click, in column [C] this will display a listbox.
    If you move away from column [C], the listbox disappears.
    This demo file uses Worksheet Code (right-click on the tab [Main] and select View Code to see it)
    I also use some named cells etc etc etc

    zeddy
    Attached Files Attached Files

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Zeddy, I'm at work where they have me working on a Mac and some of your features are not supported on a Mac so I'll have to try it again when I get home this evening. Thanks. I'll post here how it goes.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi jpzinn

    The rules here are that if you don't tell us what Excel version etc you are using, we can assume anything we like!
    Then, when you tell us, we try and help again.

    zeddy

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Zeddy, It works on my PC at home but it isn't really what I was looking for. I got it to work the way I want by using two columns but I was hoping to do it in one. Unfortunately, it works fine on a PC but I can't get it to work on the Mac at work. I don't know if there is a work-around or not. I've attached my solution so you can see what I'm after. If you click the arrow in column N, then choose a category, the ID will pop into column O. I wanted the ID to replace the category in N because I don't need the category title, just the ID but I can make it work like this with two columns if I have to...
    Attached Files Attached Files

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts

    Using Combobox for Data Validation

    jpzinn,

    Here is some code that will allow the user to select a category from a dropdown when clicking in column N but will insert the ID in the selected cell instead of the selected category. Just drop a combobox (active X) anywhere on the sheet called Category. The code will take care of the rest.

    HTH,
    Maud

    Place the following code at the top of the worksheet module:
    Code:
    Private cell As Range
    
    
    Private Sub Category_Change()
    On Error GoTo errorhandler
    With Worksheets("data")
        cell = .Cells(Category.ListIndex + 1, "K")
    End With
    errorhandler:
    Category.Visible = False
    End Sub
    
    
    Private Sub Worksheet_Activate()
    Category.Visible = False
    End Sub
    
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("N2:N50")) Is Nothing Then
        Category.List = Sheets("data").Range("J1:J5").Value
        Category.Value = ""
        Category.Width = 116.25
        Category.Height = 15
        Category.Top = Target.Top
        Category.Left = Target.Left
        Category.Visible = True
        Set cell = Target
    Else:
        Category.Visible = False
    End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2016-07-31 at 11:28. Reason: added title

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks so much. I figured out what I was doing wrong on the Mac and it now works as I expected. I'm using two columns and the first has a validation list on it so I choose the category with that dropdown and then there is a vlookup function in the second column that gets the ID based on whatever was chosen in the first column.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •