Dependent Data Validation with VBA

SChalaev · May 7th 2015

Hi guys,
I'm trying to set up two dependent data validation in a worksheet. Based on the selection in the first data validation cell, the second cell data validation list is adjusted.
I've had a look at INDIRECT () function in other tutorials but my data is not set up to allow the use of that function so I'm turning to VBA as an alternative.

In a simple Test sheet:

First data validation cell has a dynamic named range in C3: =OFFSET(Data!$B$4,,,COUNTA(Data!$B$4:$B$30))
Second data validation cell is created by this code in VBA in E3 (that I have found in another tutorial) that basically pulls the corresponding dynamic named range based on selection in C3.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("C3")
If Not Intersect(Target, Range("C3")) Is Nothing Then
With Range("E3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Rng.Select
End If
End Sub

Display More

So far it works only in E3 depending on the choice in only C3......but if I have data validations going down to, say, C10....how can I populate the second data validation (by changing range in VBA??) to, for example, E10?
Many thanks

SChalaev · May 7th 2015

Re: Dependent Data Validation with VBA

This is a link to the test file if that helps:
https://www.dropbox.com/s/0z6t…lidation%20Test.xlsm?dl=0

SChalaev · May 7th 2015

Re: Dependent Data Validation with VBA

Managed to extend first and second data validations by bloating up the WorksheetChange sub. Any way or ideas to shorten the code because I probably have >100 cells to validate?

Many thanks.

Code

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
WorksheetChanged Target
End Sub




Private Sub WorksheetChanged(ByVal Target As Range)




Dim PrevCell As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rng5 As Range
Dim Rng6 As Range




Set PrevCell = ActiveCell
Set Rng1 = Range("C3")
Set Rng2 = Range("C4")
Set Rng3 = Range("C5")
Set Rng4 = Range("C6")
Set Rng5 = Range("C7")
Set Rng6 = Range("C8")




If Not Intersect(Target, Range("C3")) Is Nothing Then
If Rng1 > 1 Then
With Range("E3").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng1
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Rng1.Select
End With
End If
End If




If Not Intersect(Target, Range("C4")) Is Nothing Then
If Rng2 > 1 Then
With Range("E4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng2
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Rng2.Select
End With
End If
End If


..... etc


..... etc


..... etc




If Not Intersect(Target, Range("C8")) Is Nothing Then
If Rng6 > 1 Then
With Range("E8").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Rng6
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
Rng6.Select
End With
End If
End If




Application.Goto PrevCell




End Sub

Display More

**StephenR** · May 7th 2015

Re: Dependent Data Validation with VBA

Try this in your worksheet change code - don't see the need to add another procedure:

Code

Private Sub Worksheet_Change(ByVal Target As Range)


Dim PrevCell As Range


Set PrevCell = ActiveCell


If Not Intersect(Target, Range("C3:C8")) Is Nothing Then
    With Target.Offset(, 2).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=" & Target
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        Target.Select
    End With
End If


Application.Goto PrevCell


End Sub

Display More

SChalaev · May 7th 2015

Re: Dependent Data Validation with VBA

Thank you Stephen, that works.

I've used the following because it helped bypass error in case Range("C3:C8") got emptied on the sheet by highlighting that range and selecting Clear Content.

Code

If RngX > 1 Then
...
...
End If

How can I adapt it or similar method to your code?

**StephenR** · May 7th 2015

Re: Dependent Data Validation with VBA

Ah yes missed that, you could put:

Code

If target.value<>"" then

SChalaev · May 7th 2015

Re: Dependent Data Validation with VBA

Thanks again

**cytop** · May 7th 2015

Re: Dependent Data Validation with VBA

Excuse me asking - but does the validation formula not throw an error...?

Code

... Operator:= xlBetween, Formula1:="=" & Target

Dependent Data Validation with VBA

Participate now!

Share