Adding Items to Dropdown list

[music_al]

How do I add an item to a drop down box ?

I know how to (with a loop) to get the individual items (strings) but I dont know how to add them.


thanks in advance

Al

 

[My Aswer Is This]

Do you mean a DataValidation list?
Or A Activex Combobox

So are you saying you have some values already in the list and want to add more automatically?
Or explain more.

 

[music_al]

Yes I mean a data validation list.

 

[My Aswer Is This]

If your saying you want a DataValidation list where you can keep adding values too here is what I do:

Create a Excel Table one column wide
Enter a few values in the Table rows

I select the column where I wanted my DataValidation list.

Then in the DataValidation Dialog Box I choose List then I choose all the current rows in my Table. As I add to the Table There are more row values added to the DataValidation List.
My Table is only one column wide


I'm using Excel 2013​

 

[tonyyy]

How do I add an item to a drop down box ?

I know how to (with a loop) to get the individual items (strings) but I dont know how to add them.

So it sounds like you want to add data validation to a cell using vba. The following might get you started...

Sub Macro1()
Dim lyst As String, i As Long
''''Loop to create list
For i = 1 To 5
    If lyst <> "" Then
        lyst = lyst & "," & Cells(i, 1)
    Else
        lyst = Cells(i, 1)
    End If
Next i

''''The code below was created with the Macro Recorder
''''Add data validation; modify Formula1 to the list you just created
With Range("N1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=lyst
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
End Sub

Cheers,

tonyyy