Dynamic Validation

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,598
Office Version
  1. 365
Platform
  1. Windows
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
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
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top