Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
I WAS NOT IGNORING THE SUGGESTION IT WAS THAT THE WAY I WAS DOING IT
WAS NOT EXACTLY HOW YOU STATED I WAS DOING IT, THE WAY I WAS DOING IT WAS CONFIRMED AS OK BY YOU IN A PREVIOUS POSTING SO I WAS JUST SHOWING YOU HOW I WAS LOADING/UNLOADING SO YOU COULD SEE IF THERE WAS ANOTHER PROBLEM, THANKS FOR YOUR HELP |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
If you are refering to this comment:
The order in the original looked fine to me. What is the advantage or rightness of doing various lines of code prior to unloading userform1? the only thing being addressed there was the order (which to me appeared inconsequential). It didn't address the wisdom of or bless doing what you are doing and it today's topic. And, the code you posted does what I warned against. the fact that you do it by calling a subroutine rather than doing it directly in the event does not change that. If you want to key in on the word exactly, then change it to substantially. Rather than getting all lathered up, just ignore my post and do what you know is best. -- Regards, Tom Ogilvy wrote in message oups.com... I WAS NOT IGNORING THE SUGGESTION IT WAS THAT THE WAY I WAS DOING IT WAS NOT EXACTLY HOW YOU STATED I WAS DOING IT, THE WAY I WAS DOING IT WAS CONFIRMED AS OK BY YOU IN A PREVIOUS POSTING SO I WAS JUST SHOWING YOU HOW I WAS LOADING/UNLOADING SO YOU COULD SEE IF THERE WAS ANOTHER PROBLEM, THANKS FOR YOUR HELP |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate a list box with different data
Hi John,
As you may recall you sent your me your wb in early April to look at your problem re memory leaks and crashes. You had several userforms, each loading others something like this: In UF1 Unload Me immediately followed by lots of code, including calling various other proc's followed by - UF2.show ... and similar in the other userforms, each potentially being loaded & unloaded multiple times, and going round in circles. In some cases other forms were being loaded from proc's in normal modules which in turn had been called directly after an Unload Me statement in a form. I tried to explain why each successive form unload / load would inevitably lead to problems and advised, in particular, not to put more code after the Unload Me and suggested and how to do it. Without the benefit of seeing your project or a snippet of code, Tom correctly guessed your problem, and went on to suggest both what you should do and what you should not do. In essence, pretty much the same as I wrote to you off-line some while ago. Regards, Peter T wrote in message oups.com... I WAS NOT IGNORING THE SUGGESTION IT WAS THAT THE WAY I WAS DOING IT WAS NOT EXACTLY HOW YOU STATED I WAS DOING IT, THE WAY I WAS DOING IT WAS CONFIRMED AS OK BY YOU IN A PREVIOUS POSTING SO I WAS JUST SHOWING YOU HOW I WAS LOADING/UNLOADING SO YOU COULD SEE IF THERE WAS ANOTHER PROBLEM, THANKS FOR YOUR HELP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I populate a drop down list from Data Validation? | Excel Discussion (Misc queries) | |||
Pulling data from other wb based on data to populate dropdown list | Excel Discussion (Misc queries) | |||
Populate a column of data with a formula using a drop down list | Excel Discussion (Misc queries) | |||
populate data valadation list by criteria | Excel Worksheet Functions | |||
I wish to automatically populate cells with list data when a item. | Excel Discussion (Misc queries) |