Hints - Microsoft Excel - Invalid Entries Are Allowed

Although you have created data validation dropdown arrows on some cells, users may be able to type invalid entries. The following are the most common reasons for this.

Error Alert
If the Error Alert is turned off, users will be able to type any entry, without receiving an error message. To turn the alert on:

Blank Cells in Source List
If the source list is a named range that contains blank cells, users may be able to type any entry, without receiving an error message. To turn prevent this:

Note: If the source list contains blank cells, and is a range address, e.g. $A$1:$A$10, it will block invalid entries with Ignore blank on or off.

Data Validation on a Protected Sheet

In Excel 2000 and earlier versions, you can change the selection in a data validation dropdown, if the list is from a range on the worksheet. If the list is typed in the data validation dialog box, the selection can't be changed. 

In Excel 2002 and later versions, neither type of dropdown list can be changed if the cell is locked and the sheet is protected. 

Make the Dropdown List Temporarily Wider

The Data Validation dropdown is the width of the cell that it's in, to a minimum of about 3/4". You could use a SelectionChange event to temporarily widen the column when it's active, then make it narrower when you select a cell in another column. 

Make the Dropdown List Appear Larger

In a Data Validation dropdown list, you can't change the font or font size. 

To make the text appear larger, you can use an event procedure (three examples are shown below) to increase the zoom setting when the cell is selected. (Note: this can be a bit jumpy) 

Or, you can use code to display a combobox.

Zoom in when specific cell is selected

If cell A2 has a data validation list, the following code will change the zoom setting to 120% when that cell is selected. 

Zoom in when specific cells are selected

If several cells have a data validation list, the following code will change the zoom setting to 120% when any of those cells are selected. In this example, cells A1, B3 and D9 have data validation.

Zoom in when any cell with a data validation list is selected

The following code will change the zoom setting to 120% when any cell with a data validation list is selected.

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London