list entry help

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
have created a travel voucher form that has a 1300+ list of data of virtually every city in the country, many with multiple entries such as:

WILLIAMSBURG, VIRGINIA, 1 Apr through 31 Aug
WILLIAMSBURG, VIRGINIA, 1 Sep through 31 Mar

For each day traveled (from 1 day to 4 weeks, where each day can be a different location) , the user inputs the travel location by clicking on the data validation list box drop arrow in the cell that matches the travel day and selects the travel location from the list (it uses a VLOOKUP table to output the $$ he gets for that location and time of year).

However, the validation box is limited in that (1) you can't see the entire line (the input cell is wrapped but the list box cannot do that) and (2) the user can't go straight to the city by typing but is limited to scrolling only.

I would prefer to build a entry method that:
(1) shows the whole width of the entry (probably easily handled via a combo box but it has been a LONG time since I have used a combo box);
(2) be able to begin typing the entry (in this case "w", "wi", "wil" etc) and the list is filtered to match the starting letters
(3) places the result in the same cell from which I ran the routine, and
(4) is initiated as easily as a validation box (a command button just above each possible input cell?)

:rolleyes:

Thanks!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
roscoe,
Better dust off the combobox gloves. The functionality you are looking for is not available using the in cell validation drop down, but is default behavior for the combobox.

Cal
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
But...my limited (and not-so-recent) experience with combo boxes tells me that the typing into the box to filter the displayed items is not a built in function but requires clever programming and beyond my current skills. Any examples would be appreciated
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Roscoe,
I think the not-so-recent thing is messing you up. I just ran a quick test with an ActiveX combobox on a worksheet. It automatically displays the first correct value for the character's you have typed in. To us it would not require any programing.
1. Add a list of the entries you want displayed in the combobox.
2. Add the combobox, from the controls toolbox Commandbar(NOT FORMS TOOLBAR).
3. Right click on the control, while in design mode.
4. Select properties.
5. Set the listFillRange property to you combobox source list(A1:A1000 or whatever)
6. Set your linked cell property to the cell you want to store the selected value in. I usually put the combobox over the cell I store the value in.

HTH
Cal
 

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,016
Nope. Not what I need; because the width of the list column is much wider than the width of the cell the output will go into (I wrap the cell to make it all visible), the combobox width must be really big. Hard to do that when I have 7 consecutive boxes next to each other. I need to be in the linked cell and call the combo box to pop-up. While up it will cover significant real estate, but after I select a value it goes away.

That is slick though...will have to remember that for next time
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top