Create dropdown list with either listbox or combo box where dropdown list length is dynamic

[question610]

In Sheet 1, the user would would be choosing the options in the dropdown. I'm not sure whether to use a data validation drop down or a combo box to achieve my goals and how to do it.

The items in the dropdown list is located on Sheet 2. The length of the dropdown list will change as new items will be added and some items may be deleted. I need a drop down where the list accurately shows the items in the list, if a new item is added, then it will show up on the dropdown. I tried data validation but it only shows the top 8 items so I can't do that. I think a combo box is the best way to go but I can't get the number of dropdown lines and input range to be dynamic based on Sheet 2.

 

[MrKowz]

You can accomplish this using a combination of data validation and a worksheet_change() event in sheet2. What cell in Sheet1 holds your dropdown and what column in sheet2 holds the values?

 

[question610]

You can accomplish this using a combination of data validation and a worksheet_change() event in sheet2. What cell in Sheet1 holds your dropdown and what column in sheet2 holds the values?

Could you elaborate what you mean by worksheet_change()?

Cell B3 holds the dropdown in Sheet 1 where data validation occurs and in Sheet 2, Column A holds the values.

Thanks

 

[MrKowz]

A Worksheet_Change() event is a macro that will automatically run when a specific worksheet undergoes a change. We can constrict this macro to only run code when a specific range within that worksheet changes. In this case, we're going to use it to monitor Column A in Sheet2 for changes.

To use this, right click on the Sheet 2 tab and choose "View Code". This will open the VBA Editor. Copy/paste the below code into the window that pops up. Note the red-highlighted parts in the code. Adjust these to match your actual sheet names.

Private Sub worksheet_change(ByVal target As Range)
Dim LR As Long
If Not Intersect(target, Range("A:A")) Is Nothing Then
    LR = Sheets("[COLOR="#FF0000"][B]Sheet2[/B][/COLOR]").Range("A" & Rows.Count).End(xlUp).Row
    With Sheets("[B][COLOR="#FF0000"]Sheet1[/COLOR][/B]").Range("B3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]!$A$1:$A$" & LR
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End If
End Sub