Modeless UserForm problem

Hi,

I have a little problem.

On opening of my WorkBook I refresh my PivotTable and that takes a while so while its refreshing. So I want to launch a UserForm that basically have a loop showing progress bar similar than the Windows loading bar (i.e. a set of green vertical green bars moving in a black rectangle).

Her is the code in THisWorkbook :

Please Login or Register  to view this content.

...and here is the code in my UserForm :

Please Login or Register  to view this content.

I don't know what code to put where (UserForm, Workbook, etc..)
Can someone help me on this issue please.

Thank you for your help
Jean

Why is the Pivottable taking so long to refresh?

The problem you will have is that excel does the refresh before moving on to the next VBA command so the userform/progress bar will only be updatable once the refresh is complete, which obviously defeats the point of the progress bar.

The only thing you can do is the animated gif in a usreform, which should run whilst the refresh is going on. Although it will take longer to refresh as it's doing 2 things now.

So load userform, start animation of gif, refresh table, close userform.

So if you place a Stop command after the Set command it never gets there?

Please Login or Register  to view this content.

Is your userform called Userform?

Erm, I wouldn't expect it to get to that line following the userform creation...

As far as I know Excel doesn't run multithreaded, so won't run 2 sets of code at the same time.

So taking your code...

Please Login or Register  to view this content.

Causes this code to run:

Please Login or Register  to view this content.

The animation loop will loop indefinitly waiting for 'StopLoop' to be True, but there is nothing to set it to true.

I know that you have StopLoop=True in your main code, but if Excel doesn't multithread it won't run your main code at the same time as running this userform code.

I believe when Andy said to use an animated gif, he meant for you to use an animated Gif, not to animate your own graphic.

I could of course be wrong, but that is what it seems like to me...

I assume you have not got Option Explicit set as I would have expected your open event to look more like this.

Please Login or Register  to view this content.

As Phil points out all your userform will do is show some animation and THEN refresh the pivot table. Taking longer than before.

If you really want to display something animated then you need to use an animated gif file displayed in a webbrowser control that is displayed modeless

Once the refresh command completes unload the userform.