Can I Cascade Dependent Validations?

[RonMcK]

How 'deep' can one cascade the dependencies?

For instance, my worksheet has columns for Unit, Chapter, and Lesson. Using the instructions from the webpage MrSteve noted, I have the dependency between Units and Chapters working.

I adapted the 'dependent' column logic to create a validation for Lessons, however, it only partly works. After I enter Unit and Chapter, the Lesson will show the correct filtered dropdown list of Lessons for the selected Chapter.

The problem arises when I leave Unit and Chapter blank, then enter a ridiculous value (say '45') for Lesson number. When I go back and select values for Unit and Chapter and tab to Lesson and then tab out of Lesson, the worksheet fails to complain that the Lesson value is outside the range of acceptable values.

How to I tie lesson validation to the unit/chapter validation?

Accompanying is a copy of my worksheet with the lists I created for the dependent validations.

Thanks in advance,

[Bob Phillips]

Data validation will not complain if nothing is changed, you have to trap the input.

If you uncheck the Ignor Blank checkbox, you shouldn't be able to enter the 45 in the first place.

[lucas]

Hi Ron,
Try this, it is sheet change event code. right click on the tab for the sheet and select "view code" paste this into the module and close the vbe and try to type a large number into either the chapter or lesson column without filling out the items to the left of them......see attached example.

Problems like this is why I use a userform for data entry......you can disable the controls until the prereuisite info is inputed.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 6 Then
If Target.Offset(0, -1).Value = "" Then
MsgBox "You must enter unit and chapter data first"
Target.ClearContents
End If
End If
If Target.Column = 5 Then
If Target.Offset(0, -1).Value = "" Then
MsgBox "You must enter unit data first"
Target.ClearContents
End If
End If
Application.EnableEvents = True
End Sub

I grew up just East of Windermere lake. I have been all over the chain of lakes there as an adventurous kid. Went to grade school at Ocoee and Winter Garden. That was in the 60's. I bet it has changed a lot by now.

[lucas]

Hi Bob, the ignore blank checkbox is checked but it still allows input.....

[RonMcK]

Bob,

One of my challenges is that 'blank' is an acceptable entry so I'd prefer to leave the box checked. I'm thinking that I'll need to do something with worksheet_change code in VBA. And as I started to type that sentence, Lucas's post with code to do just that arrived. Kewl!

Let me clarify for the lurkers, entries can refer to unit only, or unit and chapter, or all three; we just can't have lesson without unit and chapter or chapter without unit.

Thanks,

[Bob Phillips]

Bob,

One of my challenges is that 'blank' is an acceptable entry so I'd prefer to leave the box checked. I'm thinking that I'll need to do something with worksheet_change code in VBA. And as I started to type that sentence, Lucas's post with code to do just that arrived. Kewl!,

Just add a blank to the list for that predecessor.

[RonMcK]

Bob,

Thanks, I'll try it with the 'blank' entry in list and un-checked box.

Cheers!

[lucas]

Bob is right of course. No code required with his suggestion.......Thanks Bob.