How do I have excel add an drop down list if the cell before it is populated with text?

**skhan5427** · 04-13-2020, 03:24 PM

I am trying to have excel generate a dynamic dropdown list that allows you to choose from the column names in the the "Run Table" Sheet ('Run Table'!$1:$1). I want this dropdown to only appear when the checkbox in A8 is checked. How do I go about doing this?

**Keebellah** · 04-13-2020, 03:46 PM

Hi, welcome to the forum.
All nice but where do you want that dropdown list to show up, which column? B ?

**mehmetcik** · 04-13-2020, 04:21 PM



Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
    
LC = Sheets("Run Table").Cells(1, Columns.Count).End(xlToLeft).Column

    With Range("D8").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Run Table'!$A$1:" & Cells(1, LC).Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
     Range("C8").Value = "Select the column in your run table that corresponds with resin type"
     Range("D8").Select
     SendKeys "%{DOWN}"
     
Else

    With Range("D8").Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Range("C8").Value = ""
         Range("D8").Select
         
 End If
    
End Sub

**skhan5427** · 04-14-2020, 03:50 PM

Followup question: how do I make the dropdown disappear if I uncheck the checkbox after checking it?

**Keebellah** · 04-13-2020, 04:59 PM

Nice code

**skhan5427** · 04-14-2020, 03:43 PM

Thank you!!

**Keebellah** · 04-14-2020, 03:58 PM

The posted code does just that.

**skhan5427** · 04-14-2020, 05:03 PM

Originally Posted by Keebellah

The posted code does just that.

I meant to say how do I have it delete the text in D13 when unchecked, I figured it out

**Keebellah** · 04-14-2020, 04:02 PM

Se here, checked and unchecked
You will have to write the rest for the other checkboxes

**Keebellah** · 04-14-2020, 05:08 PM

Great, you see, it's not at all that complicated

How do I have excel add an drop down list if the cell before it is populated with text?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How do I have excel add an drop down list if the cell before it is populated with text?

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Re: How do I have excel add an drop down list if the cell before it is populated with text

Thread Information

Users Browsing this Thread

Similar Threads

Drop Down List & Dependent Values to be populated

[SOLVED] Pre-populated drop-down list from selection made in drop-down list

[SOLVED] Drop Down List populated from Index Match

Auto lock a cell once populated from another sheet that bases its data on a drop down list

[SOLVED] Drop-down list Populated by a Subset of a larger list

Drop-down list populated by SQL recordset?

Can a cell have a drop down list and can also be auto populated

Bookmarks

Bookmarks

Posting Permissions