Pesky validation list drop-down arrows won't go away

[jackhandey]

Howdy,

I'm working on a spreadsheet that uses a lot of drop-down validation lists. I don't think it matters, but the ranges referenced by validation are all dynamic named ranges.

Usually, if you select a cell that has a validation list, you see an arrow appear to the right of the cell. That's working just fine, but there are some cells that have an "artifact" arrow even when not selected. Furthermore, the artifact arrow appears slightly dimmed and is usually offset by a fraction of an inch farther to the right. If I click that artifact arrow, it shows an empty drop-down list. Selecting a cell that has an artifact arrow shows the "good" arrow where it usually goes, but you can still see the darker artifact "behind" the actual arrow because it's offset slightly.

Over time, I'm seeing more and more cells pick up these artifacts. It's really visually ruining an otherwise badass spreadsheet.

None of the following things has helped: Clearing validation, replacing validation, deleting cell contents, cut/paste over the top with something else, closing/re-opening the file, rebooting, and swearing loudly.

The only thing that has worked is either completely deleting the row/column or deleting the cell and shifting the row or column. The problem is that these cells with validation lists have several dependencies. Deleting the cell entirely is a huge pain because of all the #REF errors it leaves behind, and they come back over time anyway!

Anybody have any experience dealing with this? It's so bloody unprofessional looking :/

Excel 2016, Windows 10

Thanks in advance!

 

[mikerickson]

Have you tried inserting a column to the left and then deleting that column?

 

[jackhandey]

Thanks for the quick reply. I hadn't tried that, but I did just now. As an example, B7 has the persistent arrow. I inserted a new column B, and the artifact followed its home cell over to C7. I deleted the new column B, and everything shifted back to where it was initially. No luck

 

[mikerickson]

Copy paste the whole sheet to a new worksheet? (then Delete the old sheet, rename the new one to the old name)

I'm puzzled too (can't replicate the error), but am thrashing about trying to find a fix.

 

[jackhandey]

Well, this is certainly the best solution yet - thank you for suggesting it. I cut/pasted the entire sheet onto a new one, deleted the old one, and renamed the new one to match the old - the formula references on other sheets adjusted because of the cut/paste and the indirect references were happy again once the sheet was renamed. The artifact arrows stayed behind and were tossed out when the old tab was deleted.

This is a real head-scratcher, regardless. And especially now that I know I can just use the new worksheet solution to patch it up when they appear again, I'd sure just like to know why it happens so I can avoid it in future designs.

The fact that the arrows linger on a specific cell even after that cell was cut and pasted to a different location is just freakin' weird.

Maybe the combination of having a sort/filter-able field of list-validated cells with a bunch of conditional formatting is too much for little old Excel to handle.

 

[mikerickson]

Are you using any VB code to create/destroy these Validation lists? Now that your sheet is set up, is its structure going to be basically stable?
If so, there's hope that the ghosts won't re-appear.

 

[jackhandey]

I'm using a bit of VB code, but not to do anything with the lists. They're all defined with offset formulas in the name manager. I do have a lockdown script that makes all but one tab veryhidden whenever the file is saved, and then another one to unhide and apply varying levels of protection based on user input (an access key kind of deal). I wonder if repeated hiding/unhiding or protecting/unprotecting could have something to do with it?

The structure is stable, but the arrows only appeared over the last few days after everything was already set up and I was testing functionality of various components. It also wasn't limited to 1 sheet - this has occurred on 3 different sheets so far.

 

[LucianRo]

Any solution find ? I have the same problem. Excel 2016, Win 8.1

 

[jackhandey]

Unfortunately, no. The arrows still keep appearing over time. It's a huge pain in the *** and this is clearly a bug.

 

[jackhandey]

So it turns out this is almost certainly a bug specific to Excel 2016. It appears to be reproducible by applying a validation list to a cell, leaving that cell selected, then saving and closing the file. Next time you open it up, the "phantom" drop-down empty list appears. I haven't actually done extensive testing to reproduce, but this thread recently showed up in the MSFT community.

The solution that ended up finally working for me, and this is really a workaround instead of a solution is two parts:

1) VBA script to loop through each worksheet and delete all msoFormControl shapes (I just ran this once - part 2 should ensure that it isn't needed anymore)
2) Second script to run before Saves that selects a "safe" cell on each sheet (move the cursor to a cell that doesn't have a drop-down)

I modified the script for my own purpose, but the reply on that post that ended up doing the trick is on the second page of that thread. I'm so freakin' relieved to have a workaround in place. Now MS just needs to fix the bug.