If "red" from a drop-down list in A1, then "certain text" in B1, else a drop-down list in B1. How?

jkalpus

New Member
Joined
Apr 17, 2004
Messages
46
If "red" from a drop-down list in A1, then "certain text" in B1, else a drop-down list in B1. How can I do this with VBA?

I want to create a drop-down list in, say A1, which returns RED, GREEN, or BLUE.
If a user selects, say RED in the drop-down list in A1, then I want another text string to be placed in B1, (E.g., "no" in B1).
If a used selects anything else from the drop-down list in A1, then I want another drop-down list in B1.

I'm almost there so any help would be most appreciated. Thanks to all ... jk
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
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 A1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
    If Target = "RED" Then
        With Range("B1")
            .ClearContents
            .Validation.Delete
            .Value = "No"
        End With
    Else
        With Range("B1")
            .ClearContents
            .Validation.Delete
            .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes, No"
        End With
    End If
End Sub
 

jkalpus

New Member
Joined
Apr 17, 2004
Messages
46
Thanks a LOT, Mumps! Works like a champ!
One follow-up question: INSTEAD of returning "Yes, No" in the drop-down list, how can I reference a NAMED list, say "choices" for the drop-down list?
Many thanks in advance ... jk
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
Created your named range and name it "Choices" then replace this line of code:
Code:
 .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="Yes, No"
with this line:
Code:
 .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Choices"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,131
Messages
5,599,906
Members
414,345
Latest member
Jonathan43

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
Top