Add to combo box in Excel VBA

You could do something like putting this in the forms open event:

ComboBox1.RowSource = "A2:" & Range("A1").End(xlDown).Address

(of course replace ComboBox1 with the name of your combo box)

That will reset the RowSource to A2 to the last contigent used cell in that column everytime the form opens
You could also use any other event if that works better for you...


:-)
ah

I did that wrong.  The correct code would be this:
ComboBox1.RowSource = "A2:" & Range("A2").End(xlDown).Address

I may not have answered your question completely anyway though.  Here is a little bit more info...

Say you have a command button that you want to use to add an item.  In the click event of the button you could have this:


Dim MyValue as string
    MyValue = InputBox("What new value do you want to add?")
    if MyValue = "" then exit sub
    Range("A2").End(xlDown).Offset(1,0).value = MyValue
    ComboBox1.RowSource = "A2:" & Range("A2").End(xlDown).Address



That will add a new value to the list that starts in A2, then reset the rowSource of the combo box to match.

Hope that helps...

:-)
ah

Ok...a change....This will do everything you want I think...There are a lot of comments (maybe you don't need them, but just in case they help you).
Anyway...the comments make it look a lot longer than it is.  Get rid of them and it will be much shorter.  Remember to change 'A2' to the first cell on the list, or the first cell where you want to start your list...(ps...my last code would not have worked if there was not at least 2 items already in the list.  I just noticed that)


Dim NewValue As String
Dim r As Range 'First cell of the list
Dim FirstAddress As String 'Variable to hold address of first cell
Dim AlreadyExists As Boolean 'True/False variable..will be set to true if value exists


    NewValue = InputBox("What new value do you want to add?") 'Set NewValue = to the value to add
    If NewValue = "" Then Exit Sub
   
    Set r = Range("A2") 'Change this to first cell of list...r is now this cell
    FirstAddress = r.Address 'this will make FirstAddress = to r's address
   
    Do Until r = "" 'loop until the first blank cell is found
        If r.Value = NewValue Then AlreadyExists = True
        Set r = r.Offset(1, 0) 'Move r down 1 row
    Loop
   
'r will now be the first empty cell in the list and 'AlreadyExists will be
'true if any of the cells values were = to NewValue, otherwise it will be false

    If AlreadyExists = True Then 'Value was found
        MsgBox ("That value already exists.  Cannot add it to the list")
        'set the RowSource to range from FirstAddress to the address directly above where r
        'is...(remember r is at the first blank cell)
        ComboBox1.RowSource = FirstAddress & ":" & r.Offset(-1, 0).Address
    Else 'Value was not found
        r.Value = NewValue
        MsgBox ("Value has been successfully added to the list")
        'set the RowSource to range from FirstAddress to r's address
        ComboBox1.RowSource = FirstAddress & ":" & r.Address
    End If
   



Let me know if you have any problems or questions...

:-)
ah

Thanks for the points and the grade!  I'm glad you have your own ideas.  Sounds like you already got it figured out and I'm glad you came up with what you want to do.  I like the catagory idea...I hadn't thought of that one...I may have to incorporate that into mine...

Good Luck!

ah