Data Validation

[tasander]

Hi

I have 3 colums all with list data validation, the last 2 columns have lists that change depending on what is selected in the first column.

This works fien however if a user selects something on either of the final 2 columns but then goes and changes their input on the first column the final 2 colums stay the same even though the validation list has changed and their original entry is not a valid selection.

I am wondering if there is a way to get the validation boxes to refresh every time column 1 is changed/selected?

Any ideas

Thanks

 

[sijpie]

Yes. You need to capture the change in the first cell and then empty the 2nd cell.

To capture a change you need to write a macro in the sheet module. To do this, right click on the sheet tab and select 'View Code'.
The macro editor will open.
Let's assume that your first column is column A
The code will first test to see if the changed cell is in column A.
Then it will empty the cells in B and C with the same row number

Copy the following code to the macro editor:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Target.Offset(0, 1).Clear
        Target.Offset(0, 2).Clear
    End If
End Sub

 

[gsbelbin]

I think that sijpie's idea to use the Worksheet_Change event to capture this but I didn't think you wanted to clear the cells. I'd use:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Cells(Target.Row, Target.Column + 1).Calculate
Cells(Target.Row, Target.Column + 2).Calculate
End If
End Sub

Cheers

 

[tasander]

Thanks