Missing Data Validation Arrows

Macros Delete Drop Down Arrows

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.

missing arrow

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.

missingarrows02

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!

missingarrows03

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.

________________

Macros Delete Drop Down Arrows http://contexturesblog.com/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.