Populating Combobox Methods

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

Is there an easier/shorter way to populate a combobox with
values in a particular range, other the method I am
using? Below is the method I use to populate combobox. I
used combobox23 as an example...

Dim rng As Range
With Worksheets(8)
Set rng = .Range("C1:C100")
End With
For Each Cell In rng
If Cell.Value <> "" Then
ComboBox23.AddItem Cell.Value
End If
Next
ComboBox23.Value = "Please Select"


Isnt there a shorter code to achieve the same goal?

Thank you

Todd Huttenstine
 
Todd,

Put the values in a worksheet range, and set the ListFillRange property
(ControlSource if it's a Userform) of the combobox to point at that range.
You would still need to initialise ListIndex.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
how do I do that?
-----Original Message-----
Todd,

Put the values in a worksheet range, and set the ListFillRange property
(ControlSource if it's a Userform) of the combobox to point at that range.
You would still need to initialise ListIndex.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Todd,

For instance, on Sheet1
A1: Todd
A2: Bob
A3: Tom
etc.

For worksheet comboboxes, go into design mode (the blue-green triangle on
the control toolbox toolbar), select the combobox, click the properties icon
(the sheet with the hand), and in the properties you will find a
ListFillRange property. In here, add a range as the normal way, that is

Sheet1!A1:A10

exit design and it's ready for use.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I put that in my rowsource property. It didnt work in
ControlSource and there was no ListFillRange property.
when I put it in Rowsource property I put it from Sheet1!
A1:A100 because it is possible to have values going down
that far. The number of values will vary, this is why I
set it to A100. But when I do that, it also shows the
empty cell values and I dont want that. If there are
blank spaces I dont want that empty value to show.
 
Todd,

Try a dynamic range, such as

OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thank you that worked in the RowSource Property, however
is there any way I can say worksheets(1) instead of
Sheet1!?

Thank you
 
Todd,

Don 't think so. This is a worksheet reference that you are setting,
whereas Worksheets(1) is VBA.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Ok last question here...

Instead of changing the actual properties in the
properties window, can I put a code in the userform
intialize event to make it do the same thing as setting
the RowSource property to OFFSET(Sheet1!A1,,,COUNTA
(Sheet1!A:A))
 
Todd,

Yes, pretty straightforward

Me.ComboBox1.RowSource = "OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"


Referring to your worksheets(1) question, becuase we are now talking VBA,
you could do this

Me.ComboBox1.RowSource = "OFFSET(" & Worksheets(1).Name &
"!A1,,,COUNTA(" & Worksheets(1).Name & "!A:A))"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Yes thank you,

This way I dont have to set any variables and makes it
much simpler.

Thanx

Todd
 
Back
Top