Unwanted drop down arrow appears

I have created a drop down list and now for some reason another drop down arrow appears in the same spot (see picture below). When I click on it (without clicking on the cell) I get a dropdown with only a blank option. Then when I click on the cell itself the arrow for the drop down list that I actually created, appears and I can choose from my drop down list (but the other drop down arrow can still be seen in the background).

How can I get rid of this unwanted drop down arrow?

Capture excel.PNG

That's really weird. I don't suppose, you can clear the rest of the spreadsheet (except the dropdown list) and upload it? (Go Advanced>Manage Attachments)

I can't figure it out. I posted for other Excel experts to take a look. I did a search for objects (thinking maybe you overlaid a combobox or something) but nothing there.

I am having a look.

I removed the validation list then selected both i1 and J1 deleting them with a shift left this appeared to get rid of it.
I'll explore some other ideas

I wonder why when I did F5 > Special > Select Objects, it doesn't select it.

Originally Posted by ChemistB

I wonder why when I did F5 > Special > Select Objects, it doesn't select it.

I wondered the same thing. It does the same in VBA: selects the True validation item but not the ghost. It might be a bug!
The validation item seems to be stuck in a cell between I1 and J1.
Unless ... goes to do more thinking

Originally Posted by tony h

I wondered the same thing. It does the same in VBA: selects the True validation item but not the ghost. It might be a bug!
The validation item seems to be stuck in a cell between I1 and J1.
Unless ... goes to do more thinking

Interesting observation:
- inserting cells(shift right) in column J moves the ghost right.
- inserting cells(shift DOWN) does not move the ghost BUT inserting a new row 1 moves the whole lot down.

But it is only when selecting all the cells between the I1 and the ghost and deleting all in one go does the ghost disappear.

davidop because when you delete cell I1 you will mess up all the formulae.
FIRST:do a FIND $I$1 and REPLACE with another cell eg $K$1 then the formulae will remain intact
SECOND: select cells I1andJ1 and DELETE shift left
THIRD: put the validation back in the cell.

Very curious indeed.

It IS recognised as an object in the VBA space - I was able to delete it via VBA.

The associated names for those two "shapes" are "Drop Down 2" and "Drop Down 3". Just in case you have other objects in there that you want to preserve, the code below should delete just those two drop down objects.

Just to those who are interested, "Drop Down 2" is the one that we are all seeing. "Drop Down 3" is not visible, and.. also ghost-ish in that when I try and make it visible, it is visible until I select a different cell - and it changes location every time I make it visible (via VBA).

Please Login or Register  to view this content.

I've identified what the user is doing to trigger this bug. It occurs when there are multiple data validation list cells selected when the workbook is saved. For example: two sheets and cells with drop down lists are selected on both sheets. The combobox will not appear on the first sheet. It will appear in the selected cells in the subsequent sheets of the workbook (whether that is one sheet or several). And yes, it is a combobox, but it has no name so you cannot delete it programmatically or select it as an object.

Preventive measures: My preferred solution is to use a workbook closing (and before save event) procedure to programmatically select cells that do not contain data validation lists. This is a sustainable solution. No corrective procedures are needed.

Corrective measures: To eliminate the combobox after it appears, you can select the cell, copy it, select a new worksheet, paste the copied cell anywhere in the new sheet, then activate the original sheet, and press delete. The vba code is as follows:

Sub removeArrow()
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect
Dim wsForm As Worksheet
Set wsForm = ActiveWorkbook.ActiveSheet
wsForm.Unprotect
ActiveWorkbook.Worksheets.Add
Dim wsTemp As Worksheet
Set wsTemp = ActiveWorkbook.ActiveSheet
wsForm.Select
Selection.Copy
wsTemp.Select
ActiveSheet.Paste
wsForm.Select
Application.CutCopyMode = False
SendKeys "{DELETE}"
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True
ActiveWorkbook.Protect Structure:=True, Windows:=False
Application.ScreenUpdating = True
End Sub

This bug doesn't exist in Office 2010. I'm using Office 2016. I can't say if it affects any other versions.

From what I've read, this is a glitch in Excel 2016. I believe that Microsoft is aware of it, but they still have issued a fix for it. I accidentally have found that if you right click the cell with the phantom drop down arrow then click on another cell and click again on the cell with the phantom arrow, the drop down list will appear as it should. I have no idea why this works but you have to do it each time you want to access an offending cell.

You are very welcome.