conditional drop down list

[K-Ching]

my problem is on sheet 2 of the sample 1 file:

if you write a value in J8 the column H3:H19 changes according to the value range in A3:A85 or range D3:D43.
and in k8 is the drop down list for range H3:H19

i need to do this action in VBA because I'll have to do this for multiple cells for example in J8, J9, J10 and so on with their respective drop down list.

thank you for your help.

[Mumps1]

Try:

Sub Test2()
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Range("A1:A7")
        If rng1 = Range("E9") Then
            With Range("F9").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$B$1:$B$7"
            End With
            Exit For
        End If
    Next rng1
    For Each rng2 In Range("A9:A15")
        If rng2 = Range("E9") Then
            With Range("F9").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$B$9:$B$15"
            End With
            Exit For
        End If
    Next rng2
End Sub

[K-Ching]

can you show me how to use it in the sheet i don't know how to add a vba in a dropdown list

[Mumps1]

Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

[K-Ching]

i followed to the letter what you said and still not working

[Mumps1]

Try the attached file. Enter your value in cell E9 and then click the 'Add Validation' button. If you want to add the validation list automatically upon entering the value in E9 without having to click the button, please let me know.

[K-Ching]

thanks it works but yes it would be nice without pressing the button

[Mumps1]

Try this attachment.

[K-Ching]

if you show me an example of how to write the macro i will write the locations for the rest but just for info the targets are
A7:A20 , K7:K20 , V7:V20
A38:A51 , K38:K51 , V38:V51

for a total of 84 targets

[Mumps1]

Replace the macro in the worksheet code module with this one:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A7:A20, A38:A51, K7:K20, K38:K51, V7:V20, V38:V51")) Is Nothing Then Exit Sub
    Dim rng1 As Range
    Dim rng2 As Range
    For Each rng1 In Range("A1:A7")
        If rng1 = Target Then
            With Cells(Target.Row, Target.Offset(0, 1).Column).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$B$1:$B$7"
            End With
            Exit For
        End If
    Next rng1
    For Each rng2 In Range("A9:A15")
        If rng2 = Target Then
            With Cells(Target.Row, Target.Offset(0, 1).Column).Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=$B$9:$B$15"
            End With
            Exit For
        End If
    Next rng2
End Sub

[K-Ching]

thx you're awsome

[Mumps1]

My pleasure. I'm glad it worked out.