Data validation in Excel 2016

Question

I have an Excel sheet that has several columns of data validation boxes that work perfectly. The problem is that suddenly, without warning, another empty data validation box randomly shows up on top of the true drop down and I cannot get rid of it and the only way to get to the true box is with arrow keys, the mouse chooses the blank box because it is on top. It has happened on several tabs and I don't know why or how to fix. All columns are contained in a table with named ranges and the data validation boxes get their info from a separate tab. The tabs are locked and there are ranges set to Allow Users to edit, mainly sort. Any ideas?????

Answer 1

Thanks Freya! Glad to hear Microsoft is aware of and is fixing the problem. The code OssieMac wrote works like a dream as a temporary fix. Cleans all sheets very nicely. I look forward for an update that contains a permanent fix for this problem. Thanks again..........

Answer 2

Hello! The Excel team has released a fix for the issue summarized below. Please make sure you have the latest update of Office. This should stop new files from getting into the bad state. Any files that have previously gotten into the bad state will still need to be repaired.

Issue summary:

For workbooks that have multiple sheets which have list based Data Validation (similar problem with total rows in tables), if the user selects a cell with a Data Validation dropdown object on the first sheet and then switches to the second sheet and select the cell that had the Data Validation dropdown object on the first sheet, on save the cell on the second sheet will permanently end up with the ghosted Data Validation drop down object (on all subsequent opens of the file). 

Repair options:

1. Safe method: delete and recreate the cells. Possibly copy to a new worksheet if individual cell recreation does not work.
2. Risky method: OssieMac has graciously posted code that repairs this state in this thread. We are not comfortable recommending code that saves your file for you, but we do want you to know that this option is available. Be aware that any valid DV dropdowns on the page won't be displayed until you save, close, and reopen the file.

Thank you for your patience, and for bringing this to our attention!

Freya

Office Newsroom

Answer 3

Kudos to whomever wrote the code. Thanks OssieMac! I shall save this code incase more pop up.....Hopefully Microsoft fixes the bug......

I was the author of the code.

Pleased for you that it has worked but it concerns me that the previous poster has said that the problem has returned.

Answer 4

Welp...That worked. Interesting also. I could see only one rogue control but the VBA found seventeen! Most had a width and or height of 0. Kudos to whomever wrote the code. Thanks OssieMac! I shall save this code incase more pop up.....Hopefully Microsoft fixes the bug......

Answer 5

This is the second time I have seen this. On the previous occasion I wrote VBA code to get rid of the "Ghost" dropdowns but the OP said that they eventually returned again.

The "Ghost" DropDowns are actually Forms control Combos that sit on top of the Data validation. I have no idea how they get there and unfortunately they cannot be simply right clicked and deleted; they have to be identified with VBA code and use the VBA code to delete them.

The code is in my second last post at the following link but it will need some modification for your requirements because it had a password and also specific worksheet names.

Code to remove Ghost dropdowns