I have sheet that allows the following entries in a list
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 -
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
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
- Date
- Category
- Type
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
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
VBA Code:
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
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