Dropdown list kind of like a ‘select’ dropdown in HTML

Topic

New Reply

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…

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

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

Reply | Quote

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.

Reply | Quote