hi, i have a list box on a userform that displays dta from a part of my
spreadsheet. the list will have a different number of rows at different
times but always the same amount of columns. I have worked out that
there will never be more then 200 rows to display so i am currently
using

private sub userform activate
listbox1.columncount = 20
listbox1.list = range("a1:u200".value
end sub

this ensure i get all the dat into the listbox. can i make it smarter
so the list box only displays rows with data in rather then all 200.
Although this method seems to work sometime whne i run the program i am
unable to select certain rows within the listbox. i.e i may have 20
rows in the listbox and i can only highlight 17 of them.

regards

john

John,

Try this

Private Sub userform_activate()
ListBox1.ColumnCount = 20
ListBox1.List = Range("A1:U" & Application.CountA(Range("A:A"))).Value
End Sub


--
HTH

Bob Phillips

wrote in message
oups.com...
hi, i have a list box on a userform that displays dta from a part of my
spreadsheet. the list will have a different number of rows at different
times but always the same amount of columns. I have worked out that
there will never be more then 200 rows to display so i am currently
using

private sub userform activate
listbox1.columncount = 20
listbox1.list = range("a1:u200".value
end sub

this ensure i get all the dat into the listbox. can i make it smarter
so the list box only displays rows with data in rather then all 200.
Although this method seems to work sometime whne i run the program i am
unable to select certain rows within the listbox. i.e i may have 20
rows in the listbox and i can only highlight 17 of them.

regards

john

bob,

thanks that works great,
i am still getting the problem of not being able to select certain rows
within the listbox, the list box is on userform2 and is accessed from
userform1, i can go back and forth through the forms and on about the
third time this happens, any ideas

john

Sure, your approach is probably screwed up. when you show useform1 and have
it show userform2 and userform2 then shows userform1 and so on, the original
userform1 has never unloaded since its event hasn't terminated.

In userform1 you should have

Private Sub Commandbutton1_Click()
me.hide
userform2.show
me.show
end Sub

In userform2, the button should unload userform2.

I suspect you have


In Userform1

Private Sub commandButton1_Click()
unload me
Userform2.show
End sub

in Userform2

Private Sub Commandbutton1_Click
unload me
Userform1.Show
End sub

Userform1 can't terminate until userform2 terminates. Userform2 can't
terminate until userform1 terminates. then things start to get screwed up.

--
Regards,
Tom Ogilvy



wrote in message
oups.com...
bob,

thanks that works great,
i am still getting the problem of not being able to select certain rows
within the listbox, the list box is on userform2 and is accessed from
userform1, i can go back and forth through the forms and on about the
third time this happens, any ideas

john

tom,

this is how i have it

code from userform1

Private Sub CommandButton1_Click()
call results
end sub


then from the results sub

sub results ()

unload userform1

various code

userform2.show
end sub

then from userform2

Private Sub CommandButton1_Click()

call searchagain ()

end sub

sub searchagain ()

unload userform2

various code

userform1.show

end sub ()

i have check earlier from another posting that this method was ok what
do you think

john

i have just noticed that when i go to two the userform2 a second time
if the program brings up a row of data that appeared the last time i
loaded userform2, which it can sometimes its that data that i cannot
select. any ideas

john

Your doing exactly what I said causes problems.

I already gave you an idea - but if you don't feel you are having problems
or you don't feel that is the problem, then the suggestion can be ignored.

--
Regards,
Tom Ogilvy



wrote in message
oups.com...
i have just noticed that when i go to two the userform2 a second time
if the program brings up a row of data that appeared the last time i
loaded userform2, which it can sometimes its that data that i cannot
select. any ideas

john