add items to combo box
 

I feel like I should be able to work this one out, but havn't been able to.
I know I can add item to a combo box useing .additem, but I can't figure out
how to link the combo box to a cell range ie A1:A10.

I have been trying to figure it out using vb help and looking at other posts
but have not had much success.

not sure if I am on the right track, but I have been fidling with
..rowsourcce and .controlsource.

any help is greatly appreciated

Melissa

add items to combo box
 

I think I just worked it out about 60 seconds after placing the post. I was
on the right track.

not sure if it is the best way, but I have used:

..rowsource to link the the source data
..columncount to make sure the correct number of rows are displayed
and counta on the sheet to count the number of rows that need to be
displayed, as this will change.
I am now going to link the .columncount and the value from the counta formula.

It looks like this is going to work.

"beauty_bobaloo" wrote:

I feel like I should be able to work this one out, but havn't been able to.
I know I can add item to a combo box useing .additem, but I can't figure out
how to link the combo box to a cell range ie A1:A10.

I have been trying to figure it out using vb help and looking at other posts
but have not had much success.

not sure if I am on the right track, but I have been fidling with
.rowsourcce and .controlsource.

any help is greatly appreciated

Melissa

add items to combo box
 

Ok I have it working great now exept that I want to be able to change the
source range when the range changes. I havn't been able to do that how I
thought I could.

This part isn't that important, but if anyone out there knows how to do it,
I would sure appreciate knowing also.

thankyou

melissa

"beauty_bobaloo" wrote:

I think I just worked it out about 60 seconds after placing the post. I was
on the right track.

not sure if it is the best way, but I have used:

.rowsource to link the the source data
.columncount to make sure the correct number of rows are displayed
and counta on the sheet to count the number of rows that need to be
displayed, as this will change.
I am now going to link the .columncount and the value from the counta formula.

It looks like this is going to work.

"beauty_bobaloo" wrote:

I feel like I should be able to work this one out, but havn't been able to.
I know I can add item to a combo box useing .additem, but I can't figure out
how to link the combo box to a cell range ie A1:A10.

I have been trying to figure it out using vb help and looking at other posts
but have not had much success.

not sure if I am on the right track, but I have been fidling with
.rowsourcce and .controlsource.

any help is greatly appreciated

Melissa

add items to combo box
 

columncount has nothing to do with the number of rows, so hard to say.

You could use a defined name
Insert=Name=Define
Name: List1
Refersto: =Offset(sheet1!$A$1,0,0,CountA(Sheet1!A:A),4)

then
Private Sub Userform_Initialize()
Userform1.Listbox1.RowSource = "List1"
Userform1.ListBox1.columncount = 4
end sub

--
Regards,
Tom Ogilvy


"beauty_bobaloo" wrote:

Ok I have it working great now exept that I want to be able to change the
source range when the range changes. I havn't been able to do that how I
thought I could.

This part isn't that important, but if anyone out there knows how to do it,
I would sure appreciate knowing also.

thankyou

melissa

"beauty_bobaloo" wrote:

I think I just worked it out about 60 seconds after placing the post. I was
on the right track.

not sure if it is the best way, but I have used:

.rowsource to link the the source data
.columncount to make sure the correct number of rows are displayed
and counta on the sheet to count the number of rows that need to be
displayed, as this will change.
I am now going to link the .columncount and the value from the counta formula.

It looks like this is going to work.

"beauty_bobaloo" wrote:

I feel like I should be able to work this one out, but havn't been able to.
I know I can add item to a combo box useing .additem, but I can't figure out
how to link the combo box to a cell range ie A1:A10.

I have been trying to figure it out using vb help and looking at other posts
but have not had much success.

not sure if I am on the right track, but I have been fidling with
.rowsourcce and .controlsource.

any help is greatly appreciated

Melissa

add items to combo box
 

Thanks mate, worked like a charm!

I still don't totally understand columncount, but I don't need it anyway in
this situation as you poined out.

I guess I'll go do some reading to find out what it is and when to use it

thanks again,

Melissa

"Tom Ogilvy" wrote:

columncount has nothing to do with the number of rows, so hard to say.

You could use a defined name
Insert=Name=Define
Name: List1
Refersto: =Offset(sheet1!$A$1,0,0,CountA(Sheet1!A:A),4)

then
Private Sub Userform_Initialize()
Userform1.Listbox1.RowSource = "List1"
Userform1.ListBox1.columncount = 4
end sub

--
Regards,
Tom Ogilvy


"beauty_bobaloo" wrote:

Ok I have it working great now exept that I want to be able to change the
source range when the range changes. I havn't been able to do that how I
thought I could.

This part isn't that important, but if anyone out there knows how to do it,
I would sure appreciate knowing also.

thankyou

melissa

"beauty_bobaloo" wrote:

I think I just worked it out about 60 seconds after placing the post. I was
on the right track.

not sure if it is the best way, but I have used:

.rowsource to link the the source data
.columncount to make sure the correct number of rows are displayed
and counta on the sheet to count the number of rows that need to be
displayed, as this will change.
I am now going to link the .columncount and the value from the counta formula.

It looks like this is going to work.

"beauty_bobaloo" wrote:

I feel like I should be able to work this one out, but havn't been able to.
I know I can add item to a combo box useing .additem, but I can't figure out
how to link the combo box to a cell range ie A1:A10.

I have been trying to figure it out using vb help and looking at other posts
but have not had much success.

not sure if I am on the right track, but I have been fidling with
.rowsourcce and .controlsource.

any help is greatly appreciated

Melissa