Populating Combobox Methods

[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

 

[Bob Phillips]

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)

 

[Todd Huttenstine]

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)




.

 

[Bob Phillips]

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)

 

[Todd Huttenstine]

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.

 

[Bob Phillips]

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)

 

[Todd]

Thank you that worked in the RowSource Property, however
is there any way I can say worksheets(1) instead of
Sheet1!?

Thank you

 

[Bob Phillips]

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)

 

[Todd Huttenstine]

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))

 

[Bob Phillips]

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)

 

[Todd Huttenstine]

Yes thank you,

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

Thanx

Todd