Aug 12 2022 08:03 AM
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 :)
Aug 12 2022 12:54 PM - edited Aug 12 2022 01:02 PM
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
Aug 16 2022 01:26 AM
@Hans Vogelaar ooo thank you! I will give it a go :)