Phantom Drop down boxes in excel 2016

Anonymous
2016-01-14T14:47:08+00:00

I use lots of data validation drop down list and recently some phantom boxes have been appearing in my spreadsheets.  You can select the grey arrow but the list is empty.  You then select the cell to get the correct data validation list, which appears to overlay the the box.  Can anyone help?  I created a new spreadsheet from scratch and it appeared to have fixed it but today i opened the spreadsheet and once again i have phantom drop down lists!!!

I removed all the data validation from the cell and it still remained!

Can anyone help?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

70 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-02-17T17:49:27+00:00

    Thanks, I like that suggestion.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-02-17T15:39:32+00:00

    I can't help with the identification of specific drop downs however the solution to other users is to stick a small piece of code in the workbook that performs an action when anybody hits save. I get mine to select A1 in all sheets. This makes it impossible for users to save with a dropdown selected. (I have no dropdowns in A1 on any sheet).

    I agree with you that this bug needs fixing and is not good enough from MS.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-02-17T15:23:03+00:00

    I am seeing this problem, too. The other's solution of wiping out every drop-down is no good for me, I have valid drop-downs on t he page that can't be removed.

    I tried writing a sub that looks at each drop-down Shape object) on the page and asks if I want to delete it or not. The problem is that I can't tell which drop-down it is looking at. Here's what I have so far:

    Public Sub subScrubPhantomDropDowns()

        Dim intCounter As Integer

        Dim dd As Shape

        ActiveSheet.Unprotect strSheetPassword

        For intCounter = 1 To ActiveSheet.Shapes.Count

            If Left(ActiveSheet.Shapes(intCounter).Name, 9) = "Drop Down" Then

                Set dd = ActiveSheet.Shapes(intCounter)

                If MsgBox("Do you want to remove " & dd.Name & "?", vbYesNo) = vbYes Then

                    MsgBox "I will remove " & dd.Name

                End If

            End If

        Next

        ActiveSheet.Protect strSheetPassword

    End Sub

    Ideally, I'd like it to jump to the Drop-Down in question before it asks to delete, so that I can see which one it's referring to. I also need to have it unhide any hidden rows, as those drop-downs are skipped.

    I do see this as a software bug that MS needs to fix. I can certainly change my habit of not selecting a cell that has validation before saving the file, but my users are not sophisticated enough to understand not to do that, even if I explain it to them.

    Any insight into when this will be fixed would be really, really helpful. Thanks!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-02-17T10:51:07+00:00

    Hi, put the vba code in the Workbook section and set it to run everytime the workbook is saved. It works perfectly and is unnoticeable to anybody else. Once there you can forget it exists :)

    Was this answer helpful?

    0 comments
  5. Anonymous
    2016-02-16T21:16:22+00:00

    All. This solution works. A great temporary fix. I spent days with this issue as the bug stops you editing in office mobile versions.

    Was this answer helpful?

    0 comments