Basic Combobox Usage Question

rootdown42

Board Regular
Joined
Jun 8, 2005
Messages
93
I know this is a farily simple question, but I'm afraid I'm still quite a novice when it comes to VB.

My goal is to create a drop-down box with values that will contain the headers for each column in my data set. When one of the values in the combobox is selected, the data set will be sorted by that value. Whether its sorted ascending or descending depends on that particular column of data. The data set, including headers, is a range from B7:S500.

I assume that I should use a combobox for this task. Couple of questions:

1) Should I use combobox from the Control Toolbox toolbar or the Forms toolbar. What's the difference between the two?

2) Should I use the Select Case construct to perform the sort after a value has been selected from the combobox? If so, what would the test expression be?

Sorry for such basic questions, but any help is appreciated.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Each works differently:

If Column: A contains Item Numbers And
Cloumn: B contains the Item descriptions, has the Range Name "List" then:

if the ComboBox is made with View|Toolbars|ControlToolBox -- then linked cell selects the actual item from the list, and the correct formula to use is:

=VLOOKUP(LinkedCell,List,2,0)

if the ComboBox is made with View|Toolbars|Forms -- then the linked cell selects the index_number of the item, and the correct formula to use is:

=INDEX(List,index_number,2)


Both the formulas and the VB code is different for each type. The box's sit on top of your sheet so it is hard to place them in cells, I use In-Cell-Dropdown lists from the Toolbar:

Data - Validation...
Settings [Tab]: Allow=List, Source=Items by comma or Range or RangeName, Like:

1,2,3
=F1:F8
=myList
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top