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!

 

[LucianRo]

Can you please put the code to delete all msoFormControl here. Thank you very much.

 

[MARK858]

Did you read the link?

 

[jackhandey]

Yeah, I purposefully posted the link to the thread instead of just the code I used because it's worth reading through it to get an understanding of what is (or may be) going on.

 

[MARK858]

Yeah, I purposefully posted the link to the thread instead of just the code I used because it's worth reading through it to get an understanding of what is (or may be) going on.

Should've been a "@LucianRo" at the start of my post i.e.

@LucianRo, Did you read the link?

ray:

 

[jackhandey]

I should have had an @LucianRo at the start of my post as well. I was agreeing with you and replying to him/her

 

[Chandler87]

Hey guys,

New to the forum. Came across this thread in search for a solution to this problem, and found an easy solution I felt I should share.
I had the same annoying issue, and then I decided to make a copy of the sheet (by right clicking the sheet name and select 'move or copy..' and then check 'create a copy'. Right after that (without clicking anywhere else) I clicked back to the original sheet. The annoying arrow is now automatically marked, and all you have to do is click the delete key.
You can then of course delete the copy you've created.

Let me know if it works for you as well.

 

[thisguywhoisthankful]

Hey guys,

New to the forum. Came across this thread in search for a solution to this problem, and found an easy solution I felt I should share.
I had the same annoying issue, and then I decided to make a copy of the sheet (by right clicking the sheet name and select 'move or copy..' and then check 'create a copy'. Right after that (without clicking anywhere else) I clicked back to the original sheet. The annoying arrow is now automatically marked, and all you have to do is click the delete key.
You can then of course delete the copy you've created.

Let me know if it works for you as well.

I just had to sign in into this forum to say THANK YOU for this solution!!!! It worked perfectly fine, and I can't even believe that it does!!! Thanks for bringing your solution up!! You sir, are my hero of the day!

 

[Simmo1313]

Hey guys,

New to the forum. Came across this thread in search for a solution to this problem, and found an easy solution I felt I should share.
I had the same annoying issue, and then I decided to make a copy of the sheet (by right clicking the sheet name and select 'move or copy..' and then check 'create a copy'. Right after that (without clicking anywhere else) I clicked back to the original sheet. The annoying arrow is now automatically marked, and all you have to do is click the delete key.
You can then of course delete the copy you've created.

Let me know if it works for you as well.

I cant seem to get this to work. You click OK after you've checked create a copy so it creates a new copy of the page?

When I click back on the original it doesn't mark the faulty arrow? My workbook is currently riddled with these arrows and would really like a way to remove them.

Cheers

 

[mumps]

I had the same issue and after doing some research into the problem, it appears that the 'phantom arrow' problem occurs only if you are using Excel 2016 and it is a glitch in the software. Apparently, Microsoft has issued a fix which can be applied by updating your copy of Microsoft Office through Windows Update. Have a look at this link: http://answers.microsoft.com/en-us/...6/06b1f356-842d-4aa6-b452-52cad0618a86?page=6
You can avoid the problem by making sure that no cell with data validation is selected before saving the file.

 

[mumps]

I thought that I would follow up on my previous post by saying that Microsoft seemingly has still not issued a fix. I have accidentally discovered that if you right click on the cell with the phantom drop down arrow, then click on another cell and then go back and click the offending cell again, the drop down list will appear as it should so you can make a selection. This avoids using VBA to solve the problem but you have to go through these steps each time you have a problem cell.