Data Validation based on Condition(s) in Excel VBA

[jazz2409]

Hello, I was wondering if it is possible to only show items in a data validation list based on condition(s)?

Like for example, if I input AAA on cell A1, the data validation list in cll B1 will only show the named range AAA which includes 1,2,3 or if I choose the named range BBB and I input BBB on cell A1, the data validation list in cell B1 will only show the named range BBB which includes 4,5,6?

The catch here is the strings that are being written in cell A1 is dynamic. Like for example today I have 50 options to put in cell A1 all of which have their own named ranges to show in the data validation list in cell B1, then tomorrow it can only have 20 options.

Also, the number of items in those named ranges vary as well.

Any ideas?

Thank you

[jazz2409]

Here's a sample file


I also posted here just now: https://superuser.com/questions/1546073/data-validation-based-on-conditions-in-excel-vba

[paulked]

This should get you started.

In the Sheet module:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("F3"), Range(Target.Address)) Is Nothing Then
FillValid
End If
End Sub


In a code module:



Sub FillValid()
Dim lr As Long, i As Long, str As String
lr = Cells(Rows.Count, 1).End(3).Row
For i = 2 To lr
If Cells(i, 1) = Range("F3") Then
str = str & Cells(i, 2) & ","
End If
Next
str = Left(str, Len(str) - 1)
With Range("G3")
.ClearContents
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=str
End With
End Sub

[jazz2409]

Is there any chance that we could use like a formula on the data validation window itself? It will be used in lots of rows in the same column

[paulked]

Sorry, I don't understand :confused:

[Tom Jones]

You get a solution here:

I also posted here just now: https://superuser.com/questions/1546...s-in-excel-vba (https://superuser.com/questions/1546073/data-validation-based-on-conditions-in-excel-vba)


Is there any chance that we could use like a formula on the data validation window itself?

You need a helper column.

[snb]

see the attachment

[jazz2409]

see the attachment

Hello, is this applicable to lots of rows (in the same column)?

[snb]

Didn't you open the file ?

[jazz2409]

This should get you started.

In the Sheet module:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("F3"), Range(Target.Address)) Is Nothing Then
FillValid
End If
End Sub


In a code module:



Sub FillValid()
Dim lr As Long, i As Long, str As String
lr = Cells(Rows.Count, 1).End(3).Row
For i = 2 To lr
If Cells(i, 1) = Range("F3") Then
str = str & Cells(i, 2) & ","
End If
Next
str = Left(str, Len(str) - 1)
With Range("G3")
.ClearContents
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=str
End With
End Sub


Can this be applied to an x number of rows in the same column?

And also I don't understand this part:


lr = Cells(Rows.Count, 1).End(3).Row why is there a 3 at the end of the word End?

[jazz2409]

Didn't you open the file ?

sorry I only had a quick look. checking.

[jazz2409]

Hello, got this working already. Found one here: http://www.vbaexpress.com/forum/showthread.php?65116-Data-Validation&highlight=data%20validation

I changed it a little based on what I need exactly. Thank you :)

[paulked]

lr = Cells(Rows.Count, 1).End(3).Row why is there a 3 at the end of the word End?

It's just shorthand for xlUp, I type lazily at times!

[jazz2409]

I also saw something like Value2 or Formula2?
What could those be?

[paulked]

Value2 doesn't check the format of the cell and, therefore, doesn't assign the value as a date or currency.
I don't know about Formula2

[otr0n]

This should get you started.

In the Sheet module:



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("F3"), Range(Target.Address)) Is Nothing Then
FillValid
End If
End Sub


In a code module:



Sub FillValid()
Dim lr As Long, i As Long, str As String
lr = Cells(Rows.Count, 1).End(3).Row
For i = 2 To lr
If Cells(i, 1) = Range("F3") Then
str = str & Cells(i, 2) & ","
End If
Next
str = Left(str, Len(str) - 1)
With Range("G3")
.ClearContents
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=str
End With
End Sub


Hello, I know that this is a little old and I was having the same problem and this code solved it perfectly but I have just one question.
How would I write it if I want the data validation range to be in a different sheet.
by that I mean the F3 reference.
Thank you very much in advance.