Excel Data Validation Tips and Quirks

Refer to a Source List on a Different Worksheet  Use Dynamic Lists Data Validation Font Size and List Length Data Validation Dropdowns and Change Events Data Validation Dropdown Arrows Not Visible  Invalid Entries are Allowed  Data Validation on a Protected Sheet  Data Validation Dropdowns are Too Wide   Make the Dropdown List Temporarily Wider Make the Dropdown List Appear Larger    -- Zoom in when specific cell is selected    -- Zoom in when specific cells are selected    -- Zoom in when any cell with data validation is selected

Refer to a Source List on a Different Worksheet When you try to create an Excel data validation dropdown list, and refer to a source list on a different worksheet, you may see an error message: "You may not use references to other worksheets of workbooks for Data Validation criteria." To avoid this problem, name the list on the other worksheet, then refer to the named range, as described here:  Excel Data Validation  If the list is in a different workbook, you can use the technique described here: Use a List from Another Workbook

Use Dynamic Lists Some lists change frequently, with items being added or removed. If the list is the source for a Data Validation dropdown, use a dynamic formula to name the range, and the dropdown list will be automatically updated. For instructions, view this page:   Create a Dynamic Range

Data Validation Dropdowns and Change Events In Excel 2000 and later versions, selecting an item from a Data Validation dropdown list will trigger a Change event. This means that code can automatically run after a user selects an item from the list. To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList.zip file. In Excel 97, selecting an item from a Data Validation dropdown list does not trigger a Change event, unless the list items have been typed in the Data Validation dialog box. In this version, you can add a button to the worksheet, and run the code by clicking the button. To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList97.zip file. Another option in Excel 97 is to use the Calculate event to run the code. To do this, refer to the cell with data validation in a formula on the worksheet, e.g. =MATCH(C3,CategoryList,0). Then, add the filter code to the worksheet's Calculate event. To see an example, go to the Sample Worksheets page, and under the Filters heading, find Product List by Category, and download the ProductsList97Calc.zip file.

Data Validation Dropdown Arrows Not Visible Occasionally, data validation dropdown arrows are not visible on the worksheet, in cells where you know that data validation lists have been created. The following are the most common reasons for missing arrows. Active Cell Only Only the active cell on a worksheet will display a data validation dropdown arrow. To mark cells that contain data validation lists, you can colour the cells, or add a comment. If you require visible arrows for all cells that contain lists, you can use combo boxes instead of data validation, and those arrows will be visible at all times. To create a combo box, choose View|Toolbars, and select either the Control Toolbox or the Forms toolbar.
Hidden Objects If objects are hidden on the worksheet, the data validation dropdown arrows will also be hidden. To make objects visible, choose Tools|Options, and on the View tab, under Objects, select Show all.
Dropdown Option In the Data Validation dialog box, you can turn off the option for a dropdown list. To turn it back on: Select the cell that contains a data validation list Choose Data|Validation On the Settings tab, add a check mark to In-cell dropdown Click OK
Freeze Panes In Excel 97, if a Data Validation dropdown list is in a frozen pane of the window, the dropdown arrow does not appear when the cell is selected. As a workaround, use Window|Split instead of Window|Freeze Panes This problem has been corrected in later versions.   Corruption If none of the above solutions explains the missing dropdown arrows, the worksheet may be corrupted. Try copying the data to a new worksheet or workbook, and the dropdown arrows may reappear. Or, try to repair the file as you open it: In Excel, choose File | Open Select the file with the missing data validation arrows On the Open button, click the arrow Click Open and Repair When prompted, click Repair.		Without frozen panes   With frozen panes

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: Select the cell that contains a data validation list Choose Data|Validation On the Error Alert tab, add a check mark to the Show error alert after invalid data is entered box. Click OK
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: Select the cell that contains a data validation list Choose Data|Validation On the Settings tab, remove the check mark from the Ignore blank box. Click OK 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. This MSKB article has information on the previous behaviour: XL97: Error When Using Validation Drop-Down List Box http://support.microsoft.com/default.aspx?id=157484

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. For example, with Data Validation cells in column D: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 4 Then Target.Columns.ColumnWidth = 20 Else Columns(4).ColumnWidth = 5 End If End Sub To add this code to the worksheet: Right-click on the sheet tab, and choose View Code. Copy the code, and paste it onto the code module. Change the column reference from 4 to match your worksheet.

Data Validation Font Size and List Length The font size in a data validation list can't be changed, nor can its default list length, which has a maximum of eight rows. If you reduce the zoom setting on a worksheet, it can be almost impossible to read the items in the dropdown list, as in the example at right. One workaround is to use programming, and a combo box from the Control Toolbox, to overlay the cell with data validation. If the user double-clicks on a data validation cell, the combobox appears, and they can choose from it. There are instructions here.

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, as described in the previous section. 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. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub To add this code to the worksheet: Right-click on the sheet tab, and choose View Code. Copy the code, and paste it onto the code module. Change the cell reference from $A$2 to match your worksheet.

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. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("A1,B3,D9")) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If End Sub

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. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lZoom As Long Dim lZoomDV As Long Dim lDVType As Long lZoom = 100 lZoomDV = 120 lDVType = 0 Application.EnableEvents = False On Error Resume Next lDVType = Target.Validation.Type On Error GoTo errHandler If lDVType <> 3 Then With ActiveWindow If .Zoom <> lZoom Then .Zoom = lZoom End If End With Else With ActiveWindow If .Zoom <> lZoomDV Then .Zoom = lZoomDV End If End With End If exitHandler: Application.EnableEvents = True Exit Sub errHandler: GoTo exitHandler End Sub

1. Data Validation 2. Data Validation -- Create Dependent Lists 3. Hide Previously Used Items in a Dropdown List 4. Display Messages to the User 5. Use a List from Another Workbook 6. Validation Criteria Examples 7. Custom Validation Criteria Examples 8. Data Validation Tips 9. Data Validation Documentation    10 Data Validation -- Combo box      11. Data Validation -- Combo Box - Named Ranges 12. Data Validation -- Display Input Messages in a Text Box  13. Data Validation -- Dependent Dropdowns from a Sorted List   14. Data Validation -- Combo Box -- Click 15. Data Validation -- Create Dependent Lists With INDEX

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright © 2009. All rights reserved.	Debra Dalgleish