Progress bar to show that the macro is running

[frayner]

Hello all,

I'm a first time poster, and so apologies if the below is a stupid question.

I have a lengthy macro which takes Excel approximately 1 minute to work through.

At present, the user can see everything that the macro is doing, which I think makes it look a little amateurish.

What I'd like to do is present a blank screen whilst the macro is running with a progress bar to show that it hasn't frozen. From looking through various posts, I realise that progress bars that go from 1 - 100 are particularly tricky to incorporate, and so I was really looking for a progress bar that would just cycle through and repeat until the end of the macro.

Does anyone know of such a thing?

Many thank in advance for your help,

Fran

 

[Smitty]

Welcome to the Board!

Here's a method from John Walkenbach: http://support.microsoft.com/default.aspx/kb/211736/en-us

What's easier is to just use a blank sheet and update text on it throughout your code. You can use the StatusBar as well, but no one really reads that anyway.

One trick is to put Application.ScreenUpdating = False at the beginning of your code and set it to True at the end.

Hope that helps,

Smitty

 

[frayner]

Hi Smitty, thanks so much for the quick response.

The only problem with putting in a blank sheet would be that my macro uses multiple tabs copying data from one to the other, and so I wouldn't be able to keep my "progress" sheet in view. Also wouldn't this prohibit me from using the Application.ScreenUpdating = False function?

Thanks for the link, but it looks like I'd have to find a way of quantifying the progress through the macro, and I'm really not that advanced!

Any other ideas?

 

[Smitty]

You probably want to post your code then. There's very little reason to select/switch between sheets even when copying, and even if you do, screenupdating can make it transparent. So no, you're not prohibited from using it, in fact the opposite.

Smitty

 

[almagg]

well my current program takes 8 min. to run and i use a combination
of a sheet and the status bar to let the user know what is going on.

also i want people to see the code running, because i am always trying to sell my excel programming skills but a lot of companies do not use it regularly. right now i am making $25/hr and hope it lasts.

so let them see those sheets flashing etc... it makes you look good

 

[gardnertoo]

Here's the code I use (tested just this morning).

Sub update_progress()

    Sheets("Progress").Select
    Range("B2").Select
    Application.ScreenUpdating = True   'show the "Progress" page to the user
    ActiveCell.Value = howmanydaysdone
    Application.ScreenUpdating = False  'hide the action when macro goes back to "Sheet1"
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub

I've built a sheet named "Progress", on which cell A2 is how many iterations of the macro will be run (from a cell on sheet1), cell B2 is how many iterations have been run (variable named here "howmanydaysdone"), cell C2 is the percentage complete. Cells D2:CY2 (100 cells) contain the numbers 1 through 100, and cells D3:CY3 are conditional formatted to turn blue as the percentage complete in cell C2 exceeds the corresponding cell in row 2. The cells surrounding the D3:CY3 range are formatted gray, and columns C:CY are made skinny enough to all show on the screen.

It's kinda crude, but it works.

 

[Smitty]

Welcome to the Board!

Note that you should be able to pare your code down to one line:

Sheets("Progress").Range("B2").Value = howmanydaysdone

As there's generally no need for selecting.

HTH,

Smitty

 

[gardnertoo]

Nice, pennysaver, I hadn't seen that. Although, I'm running A LOT of code on "Sheet1", so I select that sheet to avoid having to specify it in every line of code. Then, in order for "Progress" to be visible during the brief period where screenupdating is true, you have to select it. Or am I wrong on that?

 

[Smitty]

Then, in order for "Progress" to be visible during the brief period where screenupdating is true, you have to select it. Or am I wrong on that?

No, you're spot on. What I'd do is send your users to the Progress sheet as soon as the code is initiated, or in the open event, then return them to an origin sheet when it's over. It really depends on what you're trying to do.

Anytime you can eliminate select statements you'll make your life easier (and your code faster!).

Smitty

 

[Jonmo1]

I select that sheet to avoid having to specify it in every line of code

You don't have to specify the sheet name in EVERY line of code...If Written well, you ushould only have to specify sheet name once.

You can use WITH

With Sheets("Sheet1")
     .Range("A1").value = "Hello"
     .Range("A2").Value = "Whatever"
     .Range("A3").Copy
End With

OR

Dim s1, s2 as Sheets
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")

s1.Range("A1").Copy
s2.Range("A1").PasteSpecial