Populating Combobox Methods
 

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

Populating Combobox Methods
 

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

Populating Combobox Methods
 

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


.

Populating Combobox Methods
 

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


.

Populating Combobox Methods
 

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


.



.

Populating Combobox Methods
 

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


.



.

Populating Combobox Methods
 

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

Thank you
-----Original Message-----
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


.



.



.

Populating Combobox Methods
 

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" wrote in message
...
Thank you that worked in the RowSource Property, however
is there any way I can say worksheets(1) instead of
Sheet1!?

Thank you
-----Original Message-----
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


.



.



.

Populating Combobox Methods
 

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



-----Original Message-----
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" wrote in
message
...
Thank you that worked in the RowSource Property, however
is there any way I can say worksheets(1) instead of
Sheet1!?

Thank you
-----Original Message-----
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


.



.



.



.

Populating Combobox Methods
 

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



-----Original Message-----
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" wrote in
message
...
Thank you that worked in the RowSource Property, however
is there any way I can say worksheets(1) instead of
Sheet1!?

Thank you
-----Original Message-----
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


.



.



.



.

Populating Combobox Methods
 

Yes thank you,

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

Thanx

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



-----Original Message-----
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" wrote in
message
...
Thank you that worked in the RowSource Property,
however
is there any way I can say worksheets(1) instead of
Sheet1!?

Thank you
-----Original Message-----
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


.



.



.



.



.