Thanks to Raystafarian I get rid of that left over Data Validation residue box by deleting all of them from the work book before exiting.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False

    'Delete all drop down lists
    For Each ws In ActiveWorkbook.Worksheets
        For Each shp In ws.Shapes
            If shp.Name Like "Drop Down *" Then
                shp.Delete
            End If
        Next shp
    Next ws

    Sheet1.Activate

    Application.ScreenUpdating = True

End Sub

=================

Original question

I'm running into an annoying bug with Excel VBA. See this screenshot. I use an Excel worksheet as a User Input form. On the form I have several places where I put Data Validation. After the user hit Enter I use this bit of code to clear the form.

For Each a In Sheets("frmBienNhan").Range("C5:K31")
    If a.Locked = False Then
        a.Value = ""
    End If
Next a

After the code ran several times these damn arrows appear. It is just like the Dropdown list of Data Validation but when you click on it the list is empty. I cannot select the cell behind it. I cannot remove it even if I use Clear all Validation or Clear All commands in Excel. This is not a big deal but it's annoying to the user because they have to move the cursor to that cell using keyboard, otherwise they cannot select the true Data Validation Dropdown List.

Any thoughts or experience on this bug are more than welcome.

Solution 1:

Holy Cow! I was struggling with this yesterday! I couldn't find any information about it on the web.

My solution was to use the immediate window in VBE (Alt + F11 then ctrl + G) to see what's on the page, so I ran something like this in a module -

Sub test()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
    Debug.Print shp.Name
Next
End Sub

It printed all the shapes on my page. Then I found the errant drop down in the immediate window. So I typed this into the immediate window -

 activesheet.shapes("Drop Down 8").delete

Just use the shape name of yours.

Solution 2:

Try seetting a.Value to vbNullString instead of "". In VBA, setting a string to "" allocates memory for a char[0] array, which contains string constants like a terminator. vbNullString returns a pointer to null, so its truly empty.

https://movefirstblog.wordpress.com/2008/06/28/vbnullstring-empty-cells-in-excel-vba/