Phantom Drop down boxes in excel 2016

Anonymous

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?

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

Anonymous

2016-04-19T19:06:08+00:00

I was able to delete the phantom boxes by copying the boxes 'behind' the phantom box and pasting them in another tab of my worksheet. When I returned to the original tab the phantom box was highlighted and I was able to delete it. So far they have not reappeared but this was my first encounter with the problem.
Was this answer helpful?

10+ people found this answer helpful.

0 comments No comments
Anonymous

2016-02-12T23:02:28+00:00

I got the following reply from Thao, the second option worked for me:

You can run the following VBA code to remove those unwanted controls.

Sub Test()

    While ActiveSheet.Shapes.Count <> 0

        ActiveSheet.Shapes(1).Delete

    Wend

End Sub

Caution:

The code above will remove all shapes (form control, drawing shapes, textboxes, OLE, etc) from the sheet. So make sure you have a backup copy of the workbook.

Caution:

If you only want to delete a specific object type (in this case, form controls), use the following code.

Sub Test()

start:

        For x = 1 To ActiveSheet.Shapes.Count

            If ActiveSheet.Shapes(x).Type = msoFormControl Then      ‘<<<<<<< you can change msoFormControl to other type

                ActiveSheet.Shapes(x).Delete

                GoTo start

            End If

        Next

End Sub
Was this answer helpful?

5 people found this answer helpful.

0 comments No comments
Anonymous

2016-06-09T17:50:52+00:00

Go to Find & Select, Selection Pane and hide the Phantom Drop Boxes. I found them at the top of the list probably because the were added last.

Hope that helps
Was this answer helpful?

3 people found this answer helpful.

0 comments No comments
Anonymous

2017-05-24T15:15:17+00:00

AMAZING! I had tried everything else, and yes, this worked first time. Thanks so much, I would have never thought of this trick on my own!
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments
Anonymous

2016-08-20T15:19:49+00:00
Hello,

I just made a test with a completely new worksheet. There I got no error. But as I wrote before this doesn't mean anything.

The next action was on an already existing sheet with 4 CommandButtons and only 3 cells with data validation. Here even all 3 of them had phantom drop down arrows and boxes. I opened by "Alt" + "F11", then "Ctrl" + "G" the (in German) "Direktbereich".
- Here I used the command "Debug.Print activesheet.shapes(x).Name".
  
  It found shapes from x=1 to 40.
  
  number 1 to 4 are the CommandButtons.
  
  number 5 to 40 are all Drop Down xx. Every number had 2 digits, starting with 14 - whatever that means?
- Then I removed all from 5 to 40 by the command "activesheet.shapes(x).delete".
Now this sheet is clean. The data validation definitions still exist. I hope it will stay correct now. But anyhow, in this sheet it was easy to keep the overview of the shapes, in other sheets it is much more difficult. - But, it is obviously that the phenomenon of one phantom drop down consists of several shapes.

regards

Harald
Was this answer helpful?

2 people found this answer helpful.

0 comments No comments