To make data entry easier, you can create drop down lists in a worksheet, using Excel’s data validation. Usually, those lists are trouble free, but sometimes the arrows disappear, for no apparent reason. See some of the reasons for that behaviour, and how to fix or avoid the problems.
Reasons for Missing Arrows
In the past, I’ve posted details on some of the reasons for missing arrows. Sometimes, it’s a simple thing, like an unchecked option in the validation settings.
But things that don’t seem related to data validation can cause missing arrows too. For example, Freeze Panes can make them disappear, in some situations.
Even a linked picture can make the arrows disappear, in some versions of Excel.
Macros and Missing Data Validation Arrows
Last week, I added another cause to the Missing Arrows page — macros that accidentally delete those arrows.
If you run a macro that deletes shapes on a worksheet, it might also delete the drop down arrow. Excel sees that arrow as a worksheet shape.
Here is my sample sheet, with a circle, a rectangle, and a drop down list.
Macro to Delete Shapes
The sample macro below has a line of code – “sh.Delete”, and it runs that code for each shape on the active sheet.
Sub DeleteShapesALL() 'WARNING: Deletes data val arrow ' if it is visible Dim sh As Shape Dim ws As Worksheet Set ws = ActiveSheet For Each sh In ws.Shapes sh.Delete Next sh End Sub
I ran that macro in my sample worksheet, and you can see the results below. The circle and rectangle are gone, and the data validation arrow is missing too!
Deleted Arrow Can’t Be Restored
There is only one data validation arrow on each Excel worksheet, and it appears in the active cell, if that cell has a drop down list.
If your macro accidentally deletes the data validation arrow on a worksheet, I don’t know of any way to get an arrow back on that worksheet.
Even if you copy a working drop down list from a different sheet, the arrow doesn’t appear on the old sheet.
You’ll have to insert a new sheet, and copy all your data, formulas and formatting onto the new sheet.
Delete Shapes Safely
To delete other worksheet shapes safely, without deleting the data validation arrows, don’t use a macro with the simple Delete method shown above.
Instead, use one of the macros to delete objects on Ron de Bruin’s website. His code checks the type for each shape, and doesn’t delete the data validation arrows.
Missing Arrows Video
This video shows the most common reasons for missing arrows. Written instructions for fixing the problems are on the Data Validation Tips page.
________________