listbox problem

I have listbox on a userform with number and the way it should work is that when I click on a number in listbox the data correspond to this number should be copied from one sheet to another sheet and gets plotted on graph. The problem is when I click on a number in the list box the data moves from one sheet to another and get plotted but only after I exit the userform sheet. I want all this done while the userform still open is there something I am doing wrong? Any help is appreciated. the file is too big to attach.

Thanks

Hi,

Difficult to comment without sight of the workbook. Any chance of cutting out most of the records, just leaving a sample and attaching here.

Is it a ListBox from the Excel Forms toolbar, or an ActiveX ListBox driven by the Visual Basic Environment?

Can you post the macro that's attached to the ListBox, and any macros attached to any of the Worksheet events, i.e. Change, Selection_Change, DeActivate etc.

Rgds

I cute out most of the data but the code is little large so the file size is still 210kb. Do you have an email I can send it to you?

Thanks

Hi,

If you zip that file it should reduce it below the 100Kb limit so that you can attach it. Just right click on the file name in Windows Explorer and choose the zip option. If you still have problems post back and I'll PM my email address.

Rgds

I already zipped it and still large also I need to attach another file where the data get copied from. So there is two files.

Hello wlaid66,

Try seeting your UserForm.ShowModal property to False. This will enable you to work in Excel while the UserForm is displayed.

Sincerely,
Leith Ross

where do I put userform1.showmodel= false. Do I put it on while the userform1 is initializing?

Never mind I found it and it is working fine. thanks for your help.

Leith,
I have one more question regarding this issue. When I click few numbers on the list box continuously I am getting a error see attached file. I have while loop and the variable i getting to one million number and I get this error.

Thanks

Hello walid66,

Without seeing your code, I can't relate the error message to anything meaningful. Can you post the code that is causing the problem?

Sincerely,
Leith Ross

I can email them to you since there are two files and they are over 500kb zipped.

Hello walid66,

My email is LeithRoss@gmail.com. I will post back what I find here at the forum. Also, I am using Excel 2003.

Sincerely,
Leith Ross

I am using excell 2007 with the extension .xlsm but let me change it to office 2003 and I will email it to you tonight.

Thanks

Hello walid66,

That works for me, thanks.

Sincerely,
Leith Ross

I emailed you the files.

Hello walid66,

There are in my InBox. Thanks.

Sincerely,
Leith Ross

Hello walid66,

The problem appears to be in your loop...

Please Login or Register  to view this content.

The ListBox control has a maximum of 64k entries (65536) which is the upper limit of an Integer data type in VBA. I don't know if this limit is higher in Excel 2007. All the loops like this one need to modified. What exactly are these loops doing?

Sincerely,
Leith Ross

the purpose of this loop is to compare the number in the listbox that is selected to the number in the table BB1_trending.xls. if the number matches then copy that row and paste it into Processing_data tab to be plotted. This will be done for all the tabs for example MOU tab, Attempts tab, AWCU tab and so on. let me know if you need anymore information.

Regards

Wally

in excel 2007 the value of "i" in the loop is reaching the million and then I am getting the error.

forget the top one here is the purpose of this loop is to compare the number in the listbox that is selected to the number in the table trending_toolxls tabs. if the number matches then copy that row and paste it into Processing_data tab to be plotted. This will be done for all the tabs for example MOU tab, Attempts tab, AWCU tab and so on. let me know if you need anymore information.

Regards

Wally

Leith,
I found the solution to the problem using the code below.
Thanks for your help.

Please Login or Register  to view this content.