list entry help

[roscoe]

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?)



Thanks!

 

[Cbrine]

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]

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]

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]

I'll give that a try...THX!

 

[roscoe]

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