add a range of items to a combobox

  • Thread starter Thread starter spence
  • Start date Start date
S

spence

is it possible to add all the items in a range
named "Users" to a combo box with one line of code
instead of having to do a
with combobox
.clear
.additem
.additem
.additem
.additem
.additem
.additem
end with
for like 20 to maybe over 50 items.
 
You can use a FOR-NEXT loop. See the example below.


Sub add_to_combobox()
Dim tmp As Integer
Dim my_range
Set my_range = Range("A1:A50") 'you can change this
With combobox
.Clear
For tmp = 1 To my_range.Cells.Count
.AddItem my_range.Cells(tmp).Value
Next
End With
End Sub


----- spence wrote: -----

is it possible to add all the items in a range
named "Users" to a combo box with one line of code
instead of having to do a
with combobox
.clear
.additem
.additem
.additem
.additem
.additem
.additem
end with
for like 20 to maybe over 50 items.
 
actually i did it in the properties for the combobox
i named the range and set the ListRows property to "RANGE
NAME" and made the name refer to "=offset(Range,0,0 COUNTA
(A:A),1) so i can add to it. but thatnx for the code, i
will keep it for reference.
 
Back
Top