Error after using VBA to clear dependent dropdowns

New Contributor

Hi,

 

Hope this is ok to post here! I thought I had been really clever as I sussed how to get my drop-downs to clear if the "parent" drop-down had changed. But now I can't edit anything else on the Sheet as it fails at the "if target column..." line even though I'm trying to delete/edit cells not in the drop-down section. Not all of it is to be drop-downs as some of it is a person's name for example and then who I send it to will fill in the options from the drop-down. There are 3 linked drop-downs (in columns 13:15) and I've used the following code in VBA that I found online:

 

Private Sub Worksheet_Change(ByVal Target as Range)

Application.EnableEvents = False

If target.column = 13 and target.validation.type = 3 then 

Target.offset(0, 1).value = "Please Select"

End if

Application.EnableEvents = True

 

Application.EnableEvents = False

If target.column = 13 and target.validation.type = 3 then 

Target.offset(0, 2).value = "Please Select"

End if

Application.EnableEvents = True

 

Application.EnableEvents = False

If target.column = 14 and target.validation.type = 3 then 

Target.offset(0, 1).value = "Please Select"

End if

Application.EnableEvents = True

 

I hope that makes sense to someone out there but sorry if this isn't allowed. Tried googling all afternoon and get no joy

 

Thank you,

 

Liz

2 Replies

@LizM123 

Like this (edited because of mistake)

 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Me.Cells.SpecialCells(xlCellTypeAllValidation), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Target.Column = 13 And Target.Validation.Type = xlValidateList Then
        Target.Offset(0, 1).Resize(1, 2).Value = "Please Select"
    End If
    If Target.Column = 14 And Target.Validation.Type = xlValidateList Then
        Target.Offset(0, 1).Value = "Please Select"
    End If
    Application.EnableEvents = True
End Sub

 

@Hans Vogelaar ooo thank you! I will give it a go