Validation List based on Option Button

[nyrangers1994]

I have a cell where I need to have a validation list. The issue is I want the validation list to change based upon an option button selection.

For example, if "Option Button 1" is selected, I want "Validation List 1" to show up. If "Option Button 2" is selected, I want "Validation List 2" to show up.

Any thoughts?

Thanks.

 

[doofusboy]

One way to do this would be to put your validation lists in columns off to the side of your regular worksheet data. Give each list a range name, like PickList1 and PickList2. You can then hide those columns if you like.

Next, paste in the following code for your OptionButtons so that they call the right validation list, which, in this example, is placed in cell C1:

Private Sub OptionButton1_Click()
    Range("C1").Validation.Delete
    Range("C1").Validation.Add Type:=xlValidateList, Formula1:="=PickList1"
    Range("C1").Validation.ShowError = False
End Sub
 
Private Sub OptionButton2_Click()
    Range("C1").Validation.Delete
    Range("C1").Validation.Add Type:=xlValidateList, Formula1:="=PickList2"
    Range("C1").Validation.ShowError = False
End Sub