PDA

View Full Version : Error Checking



teeitup
05-30-2008, 11:41 AM
I'm not sure where to start with this one.

I've two columns which are used to label data.

Column A: Has 4 possible choices for the user to select.
I've limited these selections by the use of a data validation drop down list.

Column B: Is dependent upon the selection in Column A. So when a selection is made in Col. A a subset becomes available in Col. B using data validation.

This work great at forcing users to enter only correct data. However, one problem can occur. A user can change column A after selecting column B. In which case the subset in B will not match column A.

What I need is code to run in front of my macro which will test Column A and B to ensure everything is entered correctly.

So some how it will ensure Column A is only the 4 possible selections (listed on the tab) & ensure that Column B is in the correct subset for Column A.

Thanks in advance for the help.

Doug

lucas
05-30-2008, 02:37 PM
Try this in the sheet change code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Target.Offset(0, 1).ClearContents
End If
End If
End Sub


Example attached.....I used named ranges and indirect.

mdmackillop
05-31-2008, 05:57 AM
Very neat Steve.:clap2: :clap2: :clap2:

lucas
05-31-2008, 06:46 AM
Thanks Malcolm, after looking at this I thought it was a worthwhile idea. Dependent validation like this seems a little incomplete without it.