Forum Discussion

srugh's avatar
srugh
Copper Contributor
Dec 28, 2023
Solved

Data validation dropdown list isn't working

Just recently, the data validation I have set up for my worksheets no longer shows a dropdown list or arrow in the cell for my users. You can start typing something in, and it will give options close...
excel
Formulas and Functions
  • djclements's avatar
    djclements
    Dec 28, 2023

    srugh I can't say that I have. One possible cause is that objects have been hidden for the entire workbook. Go to File > Options > Advanced, then scroll down to "Display options for this workbook:" and make sure "All" is selected under "For objects, show:".

     

    Advanced Options

     

    It's also a good idea to double-check the data validation rules to make sure "List" has been selected and the "In-cell dropdown" checkbox has been checked.

     

    Data Validation List

     

    If indeed the settings are correct, try clearing the data validation rules and recreating them from scratch.

     

    Lastly, if Freeze Panes has been applied to the neighboring column (on the right) and the horizontal scroll position is too far to the right, the drop-down arrow may appear to be missing when all you need to do is scroll back over to the left.

     

    Freeze Panes: Column D hidden by horizontal scroll position

     

    If none of these suggestions apply to your situation, it's possible the workbook has become corrupted. You could try restoring the file from a backup (if available) to see if the problem exists in a prior version of the file; or, try recreating the data validation rules in a new worksheet to see if the problem persists.

     

    Kind regards.

marcbluszcz's avatar
marcbluszcz
Copper Contributor
Mar 07, 2025

I faced the same problem with the in-cell dropdown list not appearing.

It was caused by deleting shapes one by one, as shown in the code below.

Dim obj as Object
For Each obj In ActiveSheet.Shapes
    obj.Delete
Next obj

According to the https://learn.microsoft.com/en-us/office/vba/api/excel.shapes?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm637072)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue, bulk operations involving a set of shapes (e.g. all shapes of certain type, or all shapes in the worksheet) should be performed using a ShapeRange property as shown below.

ActiveSheet.Shapes.SelectAll
Selection.ShapeRange.Delete

Resources