Data validation list disappeared

YasserKhalil
PlatinumLounger
Posts: 4902
Joined: 31 Aug 2016, 09:02

Data validation list disappeared

Post by 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
PlatinumLounger
Posts: 4902
Joined: 31 Aug 2016, 09:02

Re: Data validation list disappeared

Post by 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?

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Data validation list disappeared

Post by HansV »

Can you attach a sample workbook demonstrating the problem?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4902
Joined: 31 Aug 2016, 09:02

Re: Data validation list disappeared

Post by YasserKhalil »

Thanks a lot for reply
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Data validation list disappeared

Post by 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
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4902
Joined: 31 Aug 2016, 09:02

Re: Data validation list disappeared

Post by YasserKhalil »

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

User avatar
rory
5StarLounger
Posts: 812
Joined: 24 Jan 2010, 15:56

Re: Data validation list disappeared

Post by 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.
Regards,
Rory