increase number of items shown in drop down list?

[Guest]

Using Excel 2007 - Home edition.

Created a list and placed drop down using data validation.
The drop down window shows some of the entries with a scroll bar. Can I
increase the size of the drop down so the scroll bar goes away?

Thanks

k

 

[Guest]

I am running Windows XP and don’t have your problem. When I opened an excel
2003 file in excel 2007 I found that it takes longer to open the file without
having Excel 2007 already running (20-60 seconds).

The solution to this problem is easy and does not require any additional
software other than the standard Microsoft Windows Explorer.
1. Open Windows Explorer (explorer.exe)
2. Select Tools | Folder Options from the menu
3. Go to the File Types tab
4. Select list item XLS (Microsoft Office Excel 97-2003 Worksheet) and click
on Advanced. If the “Application used to perform action:†field the default
value should be “C:\Program Files\Microsoft Office\Office12\EXCEL.EXE†/eâ€.
Change this to “C:\Program Files\Microsoft Office\Office12\EXCEL.EXE†“%1″.
Remember to enclose the full excel.exe path and %1 in parenthesis. Since
Windows 95 this is a way to escape spaces in file names; %1 will return the
file name of that file that is being opened by double-clicking.You can
uncheck “Use DDE†but Microsoft Office Excel 2007 will bring it back to what
you see above when you next open it.

5. Just confirm (hit “OKâ€) on all the Microsoft Windows Explorer dialogs and
next time you open a Excel 2003 file in Excel 2007 without having it already
running you won’t have to wait! Problem solved!

You can try to check if this solution will solve your problem with windows
Vista.

Good Luck,

Tim

 

[Guest]

Sorry the above answer was for the previous question.

Tim

 

[Debra Dalgleish]

No, you can't change the number of items visible in a data valiation list.

 

[T. Valko]

The good news is that you can use a combo box (very similar to a data
validation drop down list) and set the number of items displayed.

The bad news is that I don't have Excel 2007 and have no idea where combo
boxes are to be found. In prevous versions they were 2 mouse clicks away.

 

[Rick Rothstein \(MVP - VB\)]

The good news is that you can use a combo box (very similar to a data

validation drop down list) and set the number of items displayed.

The bad news is that I don't have Excel 2007 and have no idea where combo
boxes are to be found. In prevous versions they were 2 mouse clicks away.

From a previous post of mine several threads down...

You are going to love how XL2007 hid this one... NOT!!!

Click the Office button (the big round thing in the upper left corner) and
select Popular on the left hand panel. Next, make sure there is a check mark
in the "Show Developer tab in the Ribbon" item and then click OK. This will
add a Developer tab to the Ribbon. Select it and click the Insert icon in
the Controls section. Your Form and ActiveX controls are both located there.

Rick

 

[Guest]

Tim, I think you answered another question and placed it on mine.
I am trying to have a data validation box open so all the available options
are visible. I seem to remember to being able to enlarge the depth of the
drop down to include all variables in 2003, but not in 2007.

 

[Debra Dalgleish]

Showing more items in the data validation list wasn't possible in
earlier versions of Excel either.

As T.Valko mentioned, you could use a combo box instead. There's a
sample file here:
http://www.contextures.com/xlDataVal11.html
that you could convert to Excel 2007 format.

 

[Jollymon67]

Rght click over the drop down menu. Select "Format Control" from menu. Click
on "Control" tab. You should see a line that says "Drop down lines:" and then
a box with a number in it. Increase the value of the number in this box to a
value equal to, or greater than the number of lines you need to display.
Select OK

 

[lenesila]

I tried this and no "Format Control" shows in the menu. I have office 2007.

 

[Gord Dibben]

The responder gave you instructions for a ComboBox from the Control Toolbox.

You are using Data Validation dropdown list so won't have the "Format
Control"

DV dropdowns cannot be extended to show all items.

Perhaps you might want to use a ComboBox in conjunction with the DV.

In that case see Debra Dalgleish's site.

http://www.contextures.on.ca/xlDataVal14.html

Note: a ComboBox also gives you autocomplete function which DV cannot
provide.


Gord Dibben MS Excel MVP

 

[MJH]

Gord,

I have tried creating a Combo Box in Excel 2007 but cannot seem to find how
to change the font of the box contents nor start the auto-complete function.
Any ideas?

 

[Jim Salb]

I found a way to increase the number of cells in a 2007 Excel spreadsheet and manage Range Names (rename, delete, edit, etc).
Click the Formulas Tab Click Name Manager click Edit
Change the cell array to what you want, or delete or create new, etc. Click Close