Data validation list disappeared

[YasserKhalil]

Hello everyone
I have two worksheets and in Sheet2 I have code in worksheet change event

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim x, ws As Worksheet, c As Range, shp As Shape, iCol As Long
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Address = "$A$2" Then
        Application.ScreenUpdating = False
            Set ws = Sheet1
            With Columns("B:Z")
                .Interior.Color = xlNone
                .Borders.Value = 0
                .ClearContents
                .ClearComments
            End With
            x = Application.Match(Target.Value, ws.Columns(2), 0)
            If Not IsError(x) Then
                ws.Range("B4:B8").Copy Range("B3")
                For Each shp In Me.Shapes
                    If shp.Type = msoFormControl Then shp.Delete
                Next shp
                iCol = 3
                For Each c In ws.Range("E6:KQ6").Cells
                    If c.Value <> Empty Then
                        ws.Range(ws.Cells(4, c.Column), ws.Cells(8, c.Column)).Copy Cells(3, iCol)
                        ws.Cells(x, c.Column).Copy Cells(8, iCol)
                        iCol = iCol + 1
                    End If
                Next c
                ws.Cells(x, "B").Copy Range("B8")
                Range("B8").Value = Empty
            End If
        Application.ScreenUpdating = True
    End If
End Sub

In Sheet2 there is A2 where I put data validation list ``=Sheet1!$B$10:$B$35``. When I select an item from the list, the arrow of the list in cell A2 disapeared completely although the data validation list remains the same ``=Sheet1!$B$10:$B$35``
Any idea why this fault happens?

[YasserKhalil]

I have traced the code and found that this part is the cause

Code: Select all

'                For Each shp In Me.Shapes
'                    If shp.Type = msoFormControl Then shp.Delete
'                Next shp

so how can I delete the form buttons keeping the data validation list and what is the relation between both of them?

[HansV]

Can you attach a sample workbook demonstrating the problem?

[YasserKhalil]

Thanks a lot for reply

[HansV]

I didn't know that the data validation drop-down is implemented as a Form Control...
One workaround is to delete only buttons: change the declaration shp As FormControl to shp As Button, and change the loop

Code: Select all

                For Each shp In Me.Shapes
                    If shp.Type = msoFormControl Then shp.Delete
                Next shp

to

Code: Select all

                For Each shp In Me.Buttons
                    shp.Delete
                Next shp

[YasserKhalil]

This is new information for me too. Thanks a lot my tutor.

[rory]

For reasons I don't really understand, the DV dropdown is not part of the DrawingObjects collection of the sheet, so you can also iterate that instead:

Code: Select all

                For Each shp In Me.DrawingObjects
                    If shp.ShapeRange.Type = msoFormControl Then shp.Delete
                Next shp

if you needed to just delete any Form controls.