Locked choices in dropdown cell and auto go to...

Originally Posted by Getalinks

I have 2 questions regarding a drop-down menu...

By drop-down menu, I assume you mean a cell with data validation using a list. (Other way might be a combo-box control.)

1) Is there a way to make it when you type the first letter of a choice in the drop down, it cycles through to that choice instead of having to scroll each time?

Not built into Excel. It is possible to build something like that using VBA, but you would have to code all the details.

2) Is there a way to make a cell only allowed to have the data which are choices on the drop-down entered into that cell?

Yes. You set up the drop-down using Data Validation. Under "Allow:" select "List", then under "Source" enter the source of data that will be used for the list. Only blanks or data from the list will be allowed. (You can uncheck Ignore Blank if you don't want to allow blanks.)

Hi Getalinks
Answering to your 1sr question. take a look at this file. Maybe it's not what u want, but it may give some idea....hth

Originally Posted by Getalinks

Would #1 be possible to just have the cell auto-complete as you type? That's what I really wanted anyway.

It's possible but I've never done exactly the same thing. You would create a combobox control instead of using data validation. You would associate the control with the same range on a worksheet that you used to create the data validation (you could also write VBA code to populate it). You configure the combobox MatchEntry property to have the behavior you want (default is to match against every character typed, that will give you your autocomplete). Then when the user completes the selection, you have to capture that result and put it where you need it. You have to figure out how to handle things like the user types in a character that doesn't match anything in the list.

That's just a broad strategy. If you really want to pursue this provide some more details (sample workbook is always good) and I (or someone else here) might have time to write some code up for you.

Say u have list of names sorted alfabetically.
Press Ctrl+F3 and place this function in Refrers To field and name this function as DRange

=OFFSET(INDIRECT(ADDRESS(MATCH(Sheet1!$F$2&"*",Sheet1!$B$4:$B$21,0)+ROW(Sheet1!$B$3),COLUMN(Sheet1!$B$4))),,,COUNTIF(Sheet1!$B$4:$B$21,Sheet1!$F$2&"*"))

Then go to data validation and create dropdown as you usually do. Type =Drange in sourse field.

In my example just press Ctrl+F3 to see named formulae.

This turned out to be WAY easier than I thought. See attached.

This solution uses a combobox control. The combobox already supports the type-ahead feature. If you go to Design Mode, you can right-click on the combobox and select ListFillRange to set the list of data to populate the control. LinkedCell is where the result will go if you need it in a cell.

Originally Posted by Getalinks

1) Can you change the font size in the box?

Yes. I assume you are not familiar with these controls so I'll give you a little more detail than I did before.

1. In Excel 2003, right click on an empty spot on your menu/toolbar space to get a list of toolbars, then check "Control Toolbox."
2. From that toolbar, click on the leftmost icon that looks like a pencil, ruler, and triangle. That is the "Design Mode" button.
3. Then right-click on the dropdown box (actually called a combobox). From that menu, select Properties. The properties will be listed in alphabetical order.
4. Note the Font property. Click on the button to the right of the font name (has three dots in it), and you will get a box to format the font however you want--size, face, bold, etc. If you make the font larger, you will need to adjust the size of the combobox by dragging sides or corners, just like any other graphical object.
5. The close the Properties box, and click on the Design Mode button again to exit design mode.

2) Is there a way to make it so only the list choices are able to be entered in the box? (i.e. If someone enters "test" and there is no "test" choice in the choices it won't let them enter it.)

There is a way documented but it does not seem to work for me. Following instructions as above, find the "Match Required" property and set it to TRUE. However, I find this has no effect in the sample workbook I posted.

Originally Posted by Excel Help

If the MatchRequired property is True, the user cannot exit the ComboBox until the text entered matches an entry in the existing list. MatchRequired maintains the integrity of the list by requiring the user to select an existing entry.

I am going to post a new thread specific to that issue.

New thread posted here:

http://www.excelforum.com/excel-gene...ml#post2277621

Thanks to JeanRage for referring me to another thread that answered this. The solution is that the Style property must be set to fmStyleDropdownList.

However, note that the behavior will be slightly modified. The user will not see the letters he types. He will only see whatever name matches the letters he types. This is important because if he tries to type in a name that's not on the list, he won't see his own typing. He'll just see the closest fit, and to try another name he'll have to backspace. So the experience is a little counterintuitive.

The alternative would be to include some VBA code to check the value when the user attempts to leave the box, and if the name isn't in the list, give a warning message and delete his entry so he can start again. I can help with that if that's the way you want to go.

I now I'm late to the party here, and just for reference...here's a link to a file where I show a technique where I use double-data validation in a single cell. You can type the first letter and ENTER...then use the same cell again and shortened list appears starting with those letters.

https://sites.google.com/a/madrocket...te-single-list

This party never ends!

Here's an example with a little code to back up the combo box. The code runs when the user changes cell selection on the worksheet. However, moving from a cell to the combobox then back to the same cell is not detected as a selection change.

JBeaucaire's solution is more robust though possibly more difficult to implement. Consider all possibilities to see which one suits your situation best.