conditional check box/pulldown

[Duane]

Hello folks,

I'd like a check box or pulldown to only appear on the sheet if a certain value is entered into a certain cell.

If that certain value is not there, the check box or pulldown disappears.

Can this be done, and if so, how?

Thanks in advance.

[Colo]

Hi Duane,
Yes it is possible.

To use Worksheet_Change event and Validation property.

Let me know the more information about certain value and certain cell and the list you would like to show.

I'll make a sample code for you.

[Duane]

Hi Colo,

Thanks for your response.

Ok, suppose I am making a pulldown menu with the input range C1:C3, and in those cells I had:
- "Please select" in C1
- "yes" in C2
- "no" in C3

Now, in B1, if I had a formula saying =if(A1="no","Proceed?","") and I want the pulldown to only show up if Proceed is showing, (or, the pulldown only shows up if there is "no" in A1, whichever), how would we do that?

[Colo]

Hi Duane,
Just as a sample, pls try this.

Right click on the sheet tab, select View Code and Please copy this into there.
After paste, pls input A1 cell "Yes" or "No".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    '///Variable declaration

    Dim rngInput As Range

    Dim rngPlldwn As Range

    Dim strList As String

    Dim varValue



    '///Preparation

    Set rngInput = Range("A1") 'a cell you want to be a trigger

    Set rngPlldwn = Range("C1:C3") 'cells you want to add validation

    strList = "Please Select, Yes, No" 'List

    varValue = "no"



    '///Judgment

    If Application.Intersect(Target, rngInput) Is Nothing Then Exit Sub

    If Target.Value <> varValue Then

        rngPlldwn.Validation.Delete

        rngInput.Offset(1).ClearContents

    Else

        rngInput.Offset(1).Value = "Proceed?"

        rngPlldwn.Select

        '///Add Validation

        With rngPlldwn.Validation

            .Delete

            .Add 3, 1, 1, strList

            .InCellDropdown = True

            .ShowInput = True

        End With

    End If



    '///Cleaning

    Set rngInput = Nothing

    Set rngPlldwn = Nothing

End Sub

Hope this help

[Duane]

Nifty stuff. It's not EXACTLY what I needed, but it's something to learn.

Thanks much, my friend!