Regarding drop down list

O

Otiose2

New Member
Joined
Apr 12, 2018
Messages
9
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
You didn't say which cells you were dealing with so in the macro cell A1 contains the drop down list with your choices and cell B1 will have the newly created drop down lists. Change the two cells to suit your needs. Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in cell A1.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    Dim Choices As String
    Select Case Target.Value
        Case "None"
            With Range("B1")
                .Validation.Delete
                .Value = "None"
            End With
        Case "Slider"
            Choices = "D4V1, D4V2, D4V3, D4V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Turn"
            Choices = "S2V1, S2V2, S2V3, S2V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Hand"
            Choices = "T2V1, T2V2, T2V3, T2V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Flex"
            Choices = " F2V1, F2V2, F2V3, F2V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
    End Select
End Sub
 
Upvote 0
Hey Mumps,

Thanks for your great support.

In case of "None" selected in cell A1, None is selected automatically in cell b1.

But when I am selecting "Slider"/"Turn"/"Hand"/"Flex" in cell A1, I got Run-time error '1004': Application-defined or object -defined error at this line ".Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices".

Could you be again provide help in this regard.

Thanks for your kind support in advance.
 
Upvote 0
Hey Mumps,

It is working ok, I needed to update the drop down list again.

Thanks for the great support.
 
Upvote 0
Oops new trouble now.

After sheet protection this problem is occuring.

In case of "None" selected in cell A1, None is selected automatically in cell b1. This is OK

But when I am selecting "Slider"/"Turn"/"Hand"/"Flex" in cell A1, I got Run-time error '1004': Application-defined or object -defined error at this line ".Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices".
 
Upvote 0
Try this macro. It unprotects the sheet, performs its task and then protects it again.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect
    Dim Choices As String
    Select Case Target.Value
        Case "None"
            With Range("B1")
                .Validation.Delete
                .Value = "None"
            End With
        Case "Slider"
            Choices = "D4V1, D4V2, D4V3, D4V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Turn"
            Choices = "S2V1, S2V2, S2V3, S2V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Hand"
            Choices = "T2V1, T2V2, T2V3, T2V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Flex"
            Choices = " F2V1, F2V2, F2V3, F2V4"
            With Range("B1").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
    End Select
    ActiveSheet.Protect
End Sub
If you used a password to protect the sheet, the password will have to be added to the code. Replace the appropriate lines with these inserting your actual password.
Code: 
ActiveSheet.Unprotect Password:="MyPassword"
ActiveSheet.Protect Password:="MyPassword"
 
Upvote 0
Hey Mumps,

Thanks for the support.

Again I have same condition in same sheet at different cell address.

Cell B38 is having 2 options : Select from Below, None

Now If I select "None", then Cell B39 to B45 should fill "None" automatically.

If I select "Select from Below" then cell B39 should have a drop down list consisting: 380,400,420,440,None
And B40 should have a drop down list consisting: Digital,Analog, None
And B41 should have a drop down list consisting: Vision,Blind, None
And B42 should have a drop down list consisting: Conv,Regsv, None
And B43 should have a drop down list consisting: User,Admin, None
And B44 should have a drop down list consisting: Force,Newton, None
And B45 should have a drop down list consisting: Sensor,Osc, None

The above code is not working here.

Thanks for support in advance.
 
Upvote 0
Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B38")) Is Nothing Then Exit Sub
    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:="380,400,420,440,None"
            End With
            With Range("B40").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Digital,Analog, None"
            End With
            With Range("B41").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Vision,Blind, None"
            End With
            With Range("B42").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Conv,Regsv, None"
            End With
            With Range("B43").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="User,Admin, None"
            End With
            With Range("B44").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Force,Newton, None"
            End With
            With Range("B45").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Sensor,Osc, None"
            End With
    End Select
End Sub
 
Upvote 0
Thanks again for prompt reply.

But Compile error : Ambiguous name detected: Worksheet_Change is showing at this line

Private Sub Worksheet_Change(ByVal Target As Range)

Thanks for your kind support in advance.
 
Upvote 0
Hi,

This is my code:

Only Macro1 is running perfectly fine, Macro2 is not triggering anything.
Code: 
 'Macro 1'
 Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B13")) Is Nothing Then Exit Sub
    Dim Choices As String
    Select Case Target.Value
        Case "None"
            With Range("B14")
                .Validation.Delete
                .Value = "None"
            End With
            With Range("B15")
                .Validation.Delete
                .Value = "None"
            End With
        Case "Servo Gun Axis"
            Choices = "Servo"
            With Range("B14").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Slider Axis"
            Choices = "Slider"
            With Range("B14").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Turntable Axis"
            Choices = "Turntable"
            With Range("B14").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
        Case "Flex Hand"
            Choices = " Flex"
            With Range("B14").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Choices
            End With
    End Select


'Macro 2'
    
    If Intersect(Target, Range("B38")) Is Nothing Then Exit Sub
    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 Sub
Thanks for your kind support in advance.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Mirror selections from multi-select drop down list in another cell
Replies
4
Views
401
Eccymarge
E
K
  • Question Question
Looking to create a drop-down list where selecting a name brings me to a specific cell containing that name
Replies
5
Views
839
SanjayGMusafir
SanjayGMusafir
C
  • Question Question
How to set up multiple drop down lists that are dependent on the selection in a single drop down list
Replies
1
Views
826
Skybluekid
Skybluekid
J
  • Solved
Excluding a Cell range from a selection in a drop down list
Replies
3
Views
803
Georgiboy
Georgiboy
H
  • Question Question
Macro to double click cell A2 on Active Sheet once Drop down in A1 has been selected from Data Validation List
2
Replies
12
Views
1K
howard
H

Forum statistics

Threads
1,224,832
Messages
6,181,234
Members
453,026
Latest member
cknader

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top