Excel 2016 Data Validation Drop Down Arrows Disappear

Question

Split from this thread.

Hi there,

I'm encountering a similar problem.

On one sheet, the user fills in a lot of daily information using data validation.

Suddenly after years of normal use,  the drop down arrow behaviour is changed. The arrow disappears shortly after the cell is selected.

A short search on support forums, has given the answer the the cause. It seems that people are encountering this for years without any proper solution from Microsoft so far.

The erroneous drop down arrow behavior started after I inserted the use of a camera tool on between 2 other sheets on the workbook.

Removing the camera tool resolves this error, still that can't be the right solution?

Taking into account the history of this bug, it would surprise me if it has anything to do with excel or windows version.

For the record, using windows 8.1 and excel 2016. But colleagues using this workbook with windows 10 and excel 2016 encounter the same issue.

https://chandoo.org/forum/threads/data-validation-issues-with-linked-pictures-in-excel-2013.22420/

https://answers.microsoft.com/en-us/office/forum/office\_2013\_release-excel/excel-2013-data-validation-drop-down-arrows/7a07385e-eb50-44fd-8fac-28b9b42e3440?db=5

Answer 1

Hi T_D_C,

I have the same issue on Exec 2016 and Win7.  Drop down list arrow flashes then disappears.  Here is a workaround that works for me until the MSFT folks distribute a fix.  

I created a VBA selection change and was going to set the shape visibility to 'True' when the cursor moved into one of my 22 drop down list cells.  But I found just checking the intersection of any cell on the sheet caused it to work.

Private Sub Worksheet_SelectionChange(ByVal target As Range)

    If Not Intersect(target, Range("A1")) Is Nothing Then

    End If  

End Sub

Limitation: Once a selection is made from the list, the arrow disappears.  You have to cursor out and back for the arrow to re-appear.

A non VBA workaround is to keep the Selection Pane window up and click the 'Show All' button when you cursor into a drop down list cell. 

Maybe it will work for you

Answer 2

Hi Everyone,

This issue is fixed on the latest version of Monthly channel, which is on version 1810 (11001.20074). If you are on the Semi-annual (Targeted) or Semi-annual channel, you can either:

1. Switch to the Monthly channel using the steps in this article: How to switch from Semi-Annual Channel to Monthly Channel for the Office 365 suite
2. Wait for the fix to come to the Semi-annual (Targeted) or Semi-annual channel.

If you do want to switch channels, we suggest contacting your local IT admin to make sure you are allowed to do so by your organization's policy.

Regards,

Sheen

Answer 3

Hi,

When I redirect the camera to a cell range on the same sheet as where the dropdown is located, the arrow of the drop down box behaves normal.

In my humble opinion this is not a work around. I'm working very day in excel workbooks with multiple sheets with the goal to have people in my organization reporting and eventually present this in dashboards.

The brilliant solution the camera function is offering to make visualizations on a sheet and reproduce it on the Dashboard sheet is quite vital.

Answer 4

Hi T_D_C,

Based on our own environment, we can see that this behavior only occurs when the camera is capturing the picture of another sheet's cells.

Please help verify this for us by using the Camera tool on cells that are on the same sheet and use it as a workaround if it works.

Regards,

Sheen

Answer 5

Hi T_D_C,

I have tested this issue from my side and I can reproduce the issue.

I can see the same behavior,  so we need to do more investigation for you.

To test the issue in the same environment as you, please help confirm the information below:

1. Record a video about how you encounter this issue.
2. Provide the related file to share with us via Private Message.
3. Capture a screenshot of your Office Product name and version according to this article: What version of Office am I using?

Best Regards,

Gloria