How to create a drop down list.

[Shaolin]

I want to create drop down menu that includes all the cities listed in the column below the cell that will have the drop down menu option. You know, that arrow on the corner of the cell.

i go to Data -> Validations

In the Allow option, I go to list.

But what do I do with source? I want to highlight everything in the column that cell is in, but for some reason, it doesn't give me that option. Did I ignore something?

Thanks

oh, I want it to sort. Is there a drop down menu to sort?

[Paleo]

1) Name the list
2) View - Tool Bar - Visual Basic
3) Tools
4) ComboBox
5) LinkedCell = the cell where you want the result
6) ListFillRange = your lists name

[Ken Puls]

Hmm... first one first... Check out Dreamboat's article on data validation (http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=29).

Does it help?

[Shaolin]

Hmm... first one first... Check out Dreamboat's article on data validation (http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=29).

Does it help?

Not really! I've done that and it doesn't give me the drop down menu I want. I want one that can sort.

For instance. In the column titled "LA Metro," there are about 30 rows. So "LA Metro" might start from column 720 to column 750, let's say, so if I want to have LA metro up on the top, I want to be able to do that with the drop down menu.

Paleo, there isn't a "Combo Box" option. Do you mean control tool box? If so, what do I do afterwards?

[Ken Puls]

Hi Shaolin,

Carlos was referrring to the Control Toolbox, yes. Once you're showing it, you can drag a combobox onto the sheet. (Hover your mouse over the pics if you are in doubt about which it is.) Once it's on the sheet, right click it and choose Properties. Then you can follow on from here:
5) LinkedCell = the cell where you want the result
6) ListFillRange = your lists name

Alternately, you can use the combobox from the forms toolbar. (Drag one onto the sheet). RIght click it and choose Format Control. On the control tab, you can set the Input Range. (Cell link is where the result goes.)

The problem is, though, that none of these options sorts by alpha. You'll need to either sort the list first, or code the one from the Control Toolbox to re-sort on click...

[Shaolin]

ok cool. I got to the properties part and went to linkedcell, and clicked on it so now it's highlighted. There is a blank area to the right of that, am I supposed to do something with it? I'm not clear on what I'm supposed to do after linkedcell.

[TheAntiGates]

Correct me if I'm mistaken, but I don't know that you can sort a listbox. AAR FWIW you can get there with



For i = lstBox1.ListCount - 1 To 0 Step -1
sMyArray(i + 1) = lstBox1.List(i)
If i = lstBox1.ListIndex Then vSelected = sMyArray(i + 1)
Next i
Call YourSpiffyArraySortingCode(sMyArray)
For i = lstBox1.ListCount - 1 To 0 Step -1
lstBox1.List(i) = sMyArray(i + 1)
If sMyArray(i + 1) = vSelected Then vDesired = i
Next i
lstBox1.ListIndex = vDesired

DoEvents might be useful too.

[Zack Barresse]

Will there be anything in the cells below your list?

[Ken Puls]

Hi Shaolin,

-In linkedcell, you'd put in the address of where you wanted the result to go... say E1
-In ListFillRange, you'd put in, I believe, the address of where your list is.
-Close properties, and select something from the lsit
-The result should be placed in cell E1 for you.

AntiGates, that's excatly what I meant when I said to code a "re-sort on click". :yes (Although, granted, I wasn't totally clear on it.)

You don't happen to have a YourSpiffyArraySortingCode procedure around do you?

[Shaolin]

Will there be anything in the cells below your list?

Yes firefytr, it's a list.

A1 is the title named "Market Group"

Below the title bar, there are over 30 different Market Groups. For example, Carolinas, Hawaii, Southern Cal, Northern Cal, New York Metro, Upstate New York, etc, etc. But each of those Market Groups are repeated 8 to 40 times, depending on the size of the market really. So New York, being the biggest market, has a lot of rows.

OK, Kpuls, I'll try it now.

[Zack Barresse]

Can you upload an example workbook? Can you move your data from one column to multiple columns? How about we have one page for your lists? You could sort in any way you wanted also. Does that sound good?

[TheAntiGates]

You don't happen to have a YourSpiffyArraySortingCode procedure around do you?Yes, but Eek, not suitable for public exposure. Maybe this little quickie will not offend the Vast Left Wing Conspiracy of coding Think-They-Know-It-Alls.


Sub BubbleSortNonproper(List() As String) 'Sort List array ascending order
Dim First As Long, Last As Long
Dim i As Long, j As Long, sTemp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If LCase(List(i)) > LCase(List(j)) Then
sTemp = List(j):List(j) = List(i):List(i) = sTemp
End If
Next j
Next i
End Sub

Dohhh! 2 declarations on the same line! Well, if you can get past the style liberties, this'll "Get Her Done." :rotlaugh:

[Shaolin]

I'll attach an example, just give me a few minutes.

There is a drop-down menu for Market Group and City/Area that sorts in various ways. Play around with it so you see what it can do.

[lucas]

Shaolin,
your example uses autofilter right? How did you get the drop downs to only show on 2 columns instead of all columns with data?

[Shaolin]

Shaolin,
your example uses autofilter right? How did you get the drop downs to only show on 2 columns instead of all columns with data?

lucas, I didn't make that spreadsheet. But I know this - Code wasn't used.

[Zack Barresse]

That's not a drop down list that sorts, it's an AutoFilter list that only encompases two columns, as Lucas points out. The lists in the drop down are always unique. What are you trying to do with it now? Can you please try and explain again?

[Shaolin]

That's not a drop down list that sorts, it's an AutoFilter list that only encompases two columns, as Lucas points out. The lists in the drop down are always unique. What are you trying to do with it now? Can you please try and explain again?
I want to know how that was done, so I can use it for another spreadsheet. I want to do the exact same thing as seen in the attachment. So, it's an AutoFilter list. Is code needed? How do I create and AutoFilter list?

all I had to do is go to data -> Filter -> AutoFilter

It only works on one column though. I will not work on two columns!

[Zack Barresse]

No, no code needed. It's a native function in Excel. It's actually one of the most under-rated and under-used functions Excel has to offer. To use it, such as it was on your spreadsheet ..

Select the range desired. The column headings MUST BE SELECTED ALONG WITH YOUR DATA (just the first row of the headers). Then click on the Data menu --> Filter --> AutoFilter. Each column header will get the drop down arrow on the right of it. It's different looking from the data validation drop down for two reasons: 1) It is not outside of the cell, it's on the right, but on the inside margin of the cell, and 2) it is always there whether or not the cell is selected or not (opposed to the data validation/list method).

One major caveat is that once employed on a sheet, you cannot use another individual instance of autofilter on another column. It's used once per sheet and that's it. You'll either need to take it off and reapply it to the columns desired or use it in code.

Using it in code is a little tricky to get the hang of but it is so much better to use this way than by hand. You actually have the option (in xl 2000 and up I believe) to use autofilter on all columns and then hide the drop down button in the desired columns. This is a very handy tool because the largest caveat (imho) is you can only have the one instance of autofilter running per sheet. This gives us the capability of creating spreadsheets that have the appearance of seperate instances, but in fact are not. Maybe it's because I hate seeing some stupid drop down box on an empty or unwanted column.

If you want some more help with using autofilter, I'd be very happy to help. :yes

[Shaolin]

it works perfectly!
Thanks!

[Zack Barresse]

Good, glad to help!!

In all actuality, you really only need select the heading rows in which you wish to encompass the autofilter, not necessarily selecting the whole range. This will be sufficient - and quicker. :yes