Regarding drop down list

[Otiose2]

Hi All,

I have a drop down list having this data:

Slider
Turn
Hand
Flex
None

My query is If I select Either Slider/Turn/Hand/Flex than next cell should have drop down list having sub categories as follows.

For Slider - D4V1, D4V2, D4V3, D4V4
For Turn - S2V1, S2V2, S2V3, S2V4
For Hand - T2V1, T2V2, T2V3, T2V4
For Flex - F2V1, F2V2, F2V3, F2V4

And If I select None than next cell should fill None automatically. I do not want dropdown in case of None selection.

Thanks in advance for your support.

 

[mumps]

The reason your are getting the "Ambiguous name detected" error is because you can have only one "Worksheet_Change" macro in a sheet. I have tidied up the code a bit and combined the two macros into one. Give this a try:

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B13,B38")) Is Nothing Then Exit Sub
    If Target.Address = "$B$13" Then
        Select Case Target.Value
            Case "None"
                With Range("B14:B15")
                    .Validation.Delete
                    .Value = "None"
                End With
            Case "Servo Gun Axis"
                Range("B14:B15").ClearContents
                With Range("B14").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Servo"
                End With
            Case "Slider Axis"
                Range("B14:B15").ClearContents
                With Range("B14").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Slider"
                End With
            Case "Turntable Axis"
                Range("B14:B15").ClearContents
                With Range("B14").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Turntable"
                End With
            Case "Flex Hand"
                Range("B14:B15").ClearContents
                With Range("B14").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Flex"
                End With
        End Select
    ElseIf Target.Address = "$B$38" Then
        Select Case Target.Value
            Case "None"
                With Range("B39:B45")
                    .Validation.Delete
                    .Value = "None"
                End With
            Case "Select from Below"
                Range("B39:B45").ClearContents
                With Range("B39").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="AC 380V,AC 400V,None"
                End With
                With Range("B40").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Input 2CH:Output 4CH, None"
                End With
                With Range("B41").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Built In Function , None"
                End With
                With Range("B42").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Differential Input , None"
                End With
                With Range("B43").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Flex, None"
                End With
                With Range("B44").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="ATI , Series, None"
                End With
                With Range("B45").Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Sensor unit, None"
                End With
        End Select
    End If
 End Sub

 

[Otiose2]

Hey Mumps,

Now everything works OK.
Again thanks for your great support.

 

[mumps]

My pleasure.