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)

"Todd Huttenstine" wrote in message
...
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

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)

"Todd Huttenstine"
wrote in message
...
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,

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" wrote in message
...
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)

"Todd Huttenstine"
wrote in message
...
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


.

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.


-----Original Message-----
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"
wrote in message
...
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)

"Todd Huttenstine"

wrote in message
...
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,

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 Huttenstine" wrote in message
...
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.


-----Original Message-----
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"
wrote in message
...
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)

"Todd Huttenstine"

wrote in message
...
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


.



.