Dynamic Validation

[mikeymay]

I have sheet that allows the following entries in a list

• Date
• Category
• Type

The Category selection is a drop-down

The Type is a drop-down based on the value in the Category defined by a Worksheet_SelectionChange looking at the Category value and adds a drop-down based on the Type selection

If upon selection of the Category cell -

• There is no Type selected, no drop-down is defined and the selected cell has no drop-down function.
• There is a Type selected, the drop-down in the cell is based on the Type selection

What I am struggling with is clearing any entries in the Category cells if the Type cell/s are cleared or changed to a different Category

As the user can remove data by either selecting one or multiple cells, I'm struggling to get the code to deal with all instances of one or multiple AND intersecting and non intersecting ranges.

I have got the dynamic validation sorted with the Worksheet_SelectionChange

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Range("BFSpent_ColType").Validation.Delete

If Target.Cells.Count > 1 Or Intersect(Target, Range("BFSpent_ColType")) Is Nothing Then
   Else
   With Target
      If .Offset(0, -1) = "" Then
         Else
         Sheets("File Data").Range("FD_Category") = Target.Offset(0, -1)
         
         With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, _
               AlertStyle:=xlValidAlertStop, _
               Operator:=xlBetween, _
               Formula1:="=D_FDBFSTypes"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = "ERROR"
            .InputMessage = ""
            .ErrorMessage = "Make a selection from the drop-down list only"
            .ShowInput = False
            .ShowError = True
         End With

      End If
   End With
End If
  
End Sub

But the code to check the Type cells is challenging.

E.g
Category selected then adjacent Type cell is assigned with the drop-down values based on the Category selection
Category selection is cleared then the adjacent Type cell is cleared

As the drop down for the Type is assigned ONLY when the Type cell is selected it is just a case of looking at what cells have been changed when a single or multiple, possibly intersecting cells have been changed.


TIA