Progress bar to show that the macro is running

frayner

New Member
Joined
Jun 29, 2007
Messages
8
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jun 29, 2007
Messages
8
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

Legend
Joined
May 15, 2003
Messages
29,536
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-known Member
Joined
Aug 21, 2002
Messages
705

ADVERTISEMENT

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

Well-known Member
Joined
Jul 24, 2007
Messages
938
Here's the code I use (tested just this morning).

Code:
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

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Welcome to the Board!

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

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

As there's generally no need for selecting.

HTH,

Smitty
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
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

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
Code:
With Sheets("Sheet1")
     .Range("A1").value = "Hello"
     .Range("A2").Value = "Whatever"
     .Range("A3").Copy
End With
OR
Code:
Dim s1, s2 as Sheets
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")

s1.Range("A1").Copy
s2.Range("A1").PasteSpecial
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,451
Members
425,548
Latest member
macjagger17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top