Dynamic Data Validation in VBA. I s it possible?

A

Ayo

I was wondering if you can help me with this. I am trying to write a code
that will changethe validation list in each cell based on changes made to
other cells.
This is what I have:

I have a range of cells, Range("B10:B21"), with validation list H,M,L.
And I have another range of cells, Range("B26:B37"), with 3 different
validation list
I.) "1,.95,.90,.85,.80,.75,.71"
II.) ".70,.65,.60,.55,.50,.45,.40,.35,.30,.25,.21"
III.) ".20,.15,.10,.05,0"

What I would like to do is this:
I want the validation list in Range("B26:B37") to change to, either I, II or
III, based on the selection in Range("B10:B21").
For example, if I select "H" in cell B10 I want the validation list in cell
"B26" to change to I.)
or if I select "M" in cell B10 I want the validation list in cell "B26" to
change to II.).

Also if I select "H" in cell B11, I want the validation list in cell "B27"
to change to I.)
or if I select "M" in cell B11, I want the validation list in cell "B27" to
change to II.).

I am including the following lines of codes to show you what I have but
itisn't working as I would like.

Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Range("B10:B21").Cells
If Range("B10") = "H" Then
Range("B26").Validation.Modify xlValidateList,
Formula1:="1,.95,.90,.85,.80,.75,.71"
ElseIf Range("B10") = "M" Then
Range("B26").Validation.Modify xlValidateList,
Formula1:=".70,.65,.60,.55,.50,.45,.40,.35,.30,.25,.21"
ElseIf Range("B10") = "L" Then
Range("B26").Validation.Modify xlValidateList,
Formula1:=".20,.15,.10,.05,0"
End If
If Range("B11") = "H" Then
Range("B27").Validation.Modify xlValidateList,
Formula1:="1,.95,.90,.85,.80,.75,.71"
ElseIf Range("B11") = "M" Then
Range("B27").Validation.Modify xlValidateList,
Formula1:=".70,.65,.60,.55,.50,.45,.40,.35,.30,.25,.21"
ElseIf Range("B11") = "L" Then
Range("B27").Validation.Modify xlValidateList,
Formula1:=".20,.15,.10,.05,0"
End If
If Range("B12") = "H" Then
Range("B28").Validation.Modify Type:=xlValidateList,
Formula1:="1,.95,.90,.85,.80,.75,.71"
ElseIf Range("B12") = "M" Then
Range("B28").Validation.Modify Type:=xlValidateList,
Formula1:=".70,.65,.60,.55,.50,.45,.40,.35,.30,.25,.21"
ElseIf Range("B12") = "L" Then
Range("B28").Validation.Modify Type:=xlValidateList,
Formula1:=".20,.15,.10,.05,0"
End If
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top