Phantom Drop down boxes in excel 2016

Harald,

  just trying to help here... by any chance you created a completely new test worksheet to validate, or you are looking to some previous example that was showing the issue?

FWUser

Hello,

I was looking to several previously existing sheets that had the error. There I removed the cells' data validation as test. The "good" definitions vanished but the "bad" phantom drop down arrows and boxes didn't. Of course you are right, I should make the test with a completely new test worksheet, but

• not every cell with data validation got that phantom staff. There is no visible system why some cells get it and others not.
• it would be awful if I would have to redo all the programming again.

regards

Harald

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

Read back toward the beginning of this long thread. The problem happens iff you have selected a cell that has list validation at the time you save. It may need to be the first list validation cell on the sheet, I don't recall. Even non-active sheets-if the selection was left at the first list validation cell and the file is saved, I believe the problem occurs there, too.

The patch you speak of is Microsoft's match, not mine. I wish I could control when they push it out, however you should have it by now. They assured me it went into global distribution. The patch does NOT remove preexisting phantom boxes. Those must be removed manually. The only truly effective way to remove the phantom boxes is by deleting the entire column or entire row. Scripts created by others don't seem to do an effective job as hoped and it is unclear why that is the case.

Historically, when the problem occurred, the phantom boxes would never occur in the worksheet in position 1 in the workbook. It was consistent on other workbooks. It would only occur in a worksheet when the cursor was left on a cell with a dropdown validation cell.

I hope your system is patched now and you have been able to fix your workbooks.