PDA

View Full Version : Progress Bar



Dowsey1977
10-28-2005, 05:51 AM
Hi,

I've currently got a macro that runs when an object is clicked. This opens up a custom made form, which then triggers a macro that opens the 'Open File' dialogue box and a sheet from a spreadsheet is copied over. After this another macro is called that moves information around on the sheets.

What I want is a progress bar to appear that runs whilst the macro is running so that the user knows that the programme isn't just hanging and doing nothing. The progress bar I found here seems ideal...

http://vbaexpress.com/kb/getarticle.php?kb_id=87#instr

However, I'm not sure where to put this in my existing code??

I would paste my code in but it is pretty long!

Any ideas?

:help :bug: :doh:

Jacob Hilderbrand
10-28-2005, 09:23 AM
The code has to go in the code section for the progress bar user form. If your code has a loop, you can update the progress bar on each iteration, otherwise, you can estimate the progress throughout the code and update accordingly.

Dowsey1977
11-01-2005, 08:57 AM
Ok...I have no loop, so would need to estimate.

Here is the code:

Option Explicit

Sub StatusBar()

Dim x As Integer
Dim MyTimer As Double

'Change this loop as needed.
For x = 1 To 250

'Dummy Loop here just to waste time.
'Replace this loop with your actual code.
MyTimer = Timer
Do
Loop While Timer - MyTimer < 0.03

Application.StatusBar = "Progress: " & x & " of 250: " & Format(x / 250, "Percent")
DoEvents

Next x

Application.StatusBar = False

End Sub


How would I amend this/add to my code to allow me to estimate this??

Thanks,
Me