Forum Discussion

JoAnne30's avatar
JoAnne30
Copper Contributor
Mar 29, 2023

Editable drop down list

Hi All

 

Just wondering if there is a way to edit/fill in a drop down list?

we have a lot of stock descriptions that are the same but with varying sizes, instead of putting all the sizes in, is there a way we can edit/add the sizes in when the option on the drop down is chosen?

 

I've not had any luck with finding this info, all editable drop down help options i find, only mention the original list being able to be edited

 

Thanks 

 

excel

2 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Mar 29, 2023

    JoAnne30 

    If I may insert an additional option with VBA :)….

    You can use VBA to create a dynamic drop-down list that changes based on the selected option.

    You can use the Worksheet_Change event to trigger a macro that updates the drop-down list with the appropriate sizes when a stock description is selected.

     

    Here’s an example of how you can do this (untested):

    Private Sub Worksheet_Change(ByVal Target As Range)
    ' Check if the changed cell is the one containing the stock description
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        ' Clear the existing drop-down list
        With Range("B1").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:=""
        End With
        
        ' Update the drop-down list with the appropriate sizes
        Select Case Range("A1").Value
            Case "Stock 1"
                Range("B1").Validation.Formula1 = "Size 1,Size 2,Size 3"
            Case "Stock 2"
                Range("B1").Validation.Formula1 = "Size 4,Size 5,Size 6"
            Case "Stock 3"
                Range("B1").Validation.Formula1 = "Size 7,Size 8,Size 9"
        End Select
    End If
End Sub

    In this example, we assume that the stock description is selected from cell A1 and the sizes are displayed in a drop-down list in cell B1. When the value in cell A1 changes, the Worksheet_Change event is triggered and the macro checks if the changed cell is A1. If it is, it clears the existing drop-down list in cell B1 and updates it with the appropriate sizes based on the selected stock description.

    You can modify this code to fit your specific needs by changing the cell references and adding more cases to handle different stock descriptions and their corresponding sizes.

     

     

    Hope it helps!

Resources