Excel 2007 : Progress Bar when Sheet Calculating

Is there a way to add some sort of progress bar to a worksheet, whilst the sheet is re-calculating?

I have just added the option to filter a large data set, (around 1 million cells) using 'radio buttons', with a lot of help from Ace_XL. The 'problem' is that it takes around six or seven seconds for the data to 'recalculate' and for the selected radio button to show as checked and so to the user, it looks as though nothing is happening. I realise there is a progress bar in the status bar, however, a) when clicking on a cell at the top of the worksheet, the user doesn't tend to look down there and b) the status bar is going to be hidden on the worksheet, so they wouldn't see it anyway.

To prevent this I would like to somehow replicate the progress bar on the worksheet itself, so the user can see that something is happening when they click the relevant radio button. Is this possible to do and if so, can anyone provide a sample or some ideas as to how this could work?

Many thanks

Ace_XL,

Okay, that looks interesting, I'll take a look and see if this could work for me...

Many thanks, again!

Hi Ace_XL,

Okay, I've taken a look at that progress bar link you kindly sent me and I've built a userform, but I have absolutely no idea how to write the required main sub routine.

Basically, 'all' I need to do is show the progress bar, whilst the sheet is re calculating, once the user selects one of the radio buttons. I timed this and it is taking 10 seconds to recalculate the sheet each time one of the three buttons is selected, hence the need for the progress bar.

Are you able to help be create a subroutine that would do this?

Hi Andy,

On re-reading the article, yes, I did see that. It's a shame as that would have been a nice solution, though two thoughts. The recalculation takes ten seconds each time, which I realise is on my laptop and could therefore be different on different users pc's, so two questions:

Question 1
Is it be possible to set up the progress bar for a fixed ten second duration? This maybe a bad approach, given my comments above, especially as it may take longer to calculate on other peoples pc's but at least it should stop them thinking nothing is happening and clicking the radio button again repeatedly or clicking one of the other radio buttons. If so, how would I write the main sub routine to do that?

Question 2
If I were to add a modeless userform with a text description, can this be done for the exact duration of the recalculation if the start and finish points are known. If so, that would work, it won't be as 'glamerous' as a progress bar, but it will servce the same purpose. If this approach is possible, how would I go about doing this. I have extremely limited VBA knowledge so I would be extremely grateful for some help or a small sample.

Many thanks

Hi Andy,

Basically I have a large data sheet which is being 'filtered' using additional columns through the use of three radio buttons. The radio buttons are set like this:

Radio Button 1
=IF(AND(AI2=TRUE,AL2=FALSE,AP2=FALSE),"*","*")

Radio Button 2 (when the first criteria is True)
=IF(AND(AI2=FALSE,AL2=TRUE,AP2=FALSE),"Genuine",IF(AND(AI2=FALSE,AL2=FALSE,AP2=TRUE),"Genuine","*"))

Radio Button 2 (when the second criteria is True)
=IF(AND(AI2=FALSE,AL2=TRUE,AP2=FALSE),"No",IF(AND(AI2=FALSE,AL2=FALSE,AP2=TRUE),"No","*"))

Radio Button 3
=IF(AND(AI2=FALSE,AL2=FALSE,AP2=TRUE),"Unlikely","*")

Each time a radio button is selected the large data sheet is filtered, based on the 'TRUE', 'FALSE', criteria above. I have no idea if this means it is using the .Calculate method. I'm afraid my knowledge of how to implement a user form and the associated code is very limited, so any assistance with how to do this would be gratefully received.

Andy,

Okay, a sample attached. I've stripped this right back to basics, so the refresh takes around 3 seconds here, rather than ten seconds on my actual workbook. The actual workbook is updating three similar sized data sheets simultaneously, so I guess it makes sense it takes around three times as long, but hopefully the principle is the same.

Basically if you click on one of the three radio buttons, this updates the data on the 'Graph Data' sheet and this is reflected in the totals on the Buttons sheet beneath the radio buttons.

I hope this makes sense. Basically I want to put something in place to show the sheet is being recalculated that appears for the duration of the recalaculation and vanishes once the sheet has recalculated, so the user can see that something is actually happening.

If this needs to be a modeless userform with a text description, that is fine. It would be great if there were a way to add an animated gif or something, like an ellipsis showing one dot, two dots, three dots, one dot, two dots, three dots etc, but this would be icing on the cake, even if it is static, then at least the user knows something is happening.

Andy,

This is 'brilliant' and works perfectly and really smoothly. I particularly like the way this now gets the click event before any cells are updated, that makes the whole thing look truly professional.

Thank you so much for taking the time to look at this for me, I can't thank you enough. This, fingers crossed, is the final piece of the puzzle in place and the picture is looking really good now.

Many many thanks...