Progress Bar

[hce]

Dear All

I have been trying to create a dialog box which will tell and indicate
to the user to wait for the macro to finish processing its code... I
looked at several examples but were still unable to figure how I can
tie the macro in to the progress bar...

Could someone be so kind to show/advice me on how I can create this...?
Say if I have Macro A and the time used to run the code depends on the
amount of data used... hence there's no way I can predict or fix the
amount of time... so how can I create a progress bar which will take
this into consideration?

If this is too complicated, could you then show me how to create a
message to ask them to wait while the macro is running and the message
will disappear once the macro is done...?

I would really appreciate any advice/help given...

Cheers

 

[Biff]

Have a look at this:

http://j-walk.com/ss/excel/tips/tip34.htm

Biff

 

[hce]

Hi

Thanks for your help... but i tried to modify it to suit m
requirements but didn't work... First Question, can we have 2 userfor
appearing at the same time because the macro is run upon clicking th
command button...

Second Question, how can I "tie" the progress bar to my macro?Part o
my macro is as below:

Sub Test

Columns("E:E").Select
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("B1").Select

Sheets("RawCCC").Select
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("C1").Select

Sheets("RawCCC").Select
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("D1").Select

Sheets("RawCCC").Select
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("E1").Select

Sheets("RawCCC").Select
Columns("O:O").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("F1").Select

Sheets("RawCCC").Select
Columns("V:V").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("G1").Select

Sheets("RawCCC").Select
Columns("X:X").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("H1").Select

Sheets("RawCCC").Select
Columns("Z:Z").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("I1").Select

Sheets("RawCCC").Select
Columns("AC:AE").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("L1").Select

Sheets("RawCCC").Select
Columns("AH:AH").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("M1").Select

Sheets("RawCCC").Select
Columns("AM:AP").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("Q1").Select

Sheets("RawCCC").Select
Columns("AS:AS").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("R1").Select

Sheets("RawCCC").Select
Columns("AY:AY").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("S1").Select

Sheets("RawCCC").Select
Columns("BC:BF").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select

Sheets("RawCCC").Visible = False
Sheets("RawEng").Visible = False
End Sub

Hence I don't understand how I can tie it in...? Can someone kindl
advice...?

Cheer

 

[Dave Peterson]

How about an easier alternative?

Just use the statusbar on the bottom right corner.

application.statusbar = "Copying E:E to B at: " & now

just pepper one of those in where you think you need it.

And reset it right before your macro finishes with:

application.statusbar = false

====
by the way, you could avoid selecting and make your code run a little faster:

Sheets("RawCCC").Select
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("RawEng").Select
ActiveSheet.Paste
Range("C1").Select

Could be replaced with:

sheets("rawwccc").columns("F:F").copy _
destination:=sheets("raweng").range("C1")

if you fix all of them, you statusbar might go by so fast that you won't even be
able to read it!

 

[hce]

Hi Dave

Thank you very much for your help! This is the difference between a
amateur and a professional... I have considered using the status ba
but the feedback was that it's not obvious enough hence a pop u
message or applet would be much preferred... Can we apply the sam
logic to a pop up message?

Cheer

 

[Dave Peterson]

You could build a userform and then show the userform, and have the code run
while it's shown.

Maybe even updating every so often.

But I'd just yell at them to look at the statusbar. My bet is that your code
works much faster without the selects.

Or you could popup a msgbox box that says:

Msgbox "Look at the statusbar for, er, status"

then your code.

 

[hce]

Hi Dave

I have experimented with the useform but because my macro is run from
userform which means a userform would be shown while the code is run..
Can I introduce another userform at this stage which would mean tha
there would be 2 userforms... at the same time? Is this possible?

If not, then my next alternative would be a msg box... Would you hav
any idea on how I can make the msg box to appear and disappear on it
own when the macro is done...? Currently, my skills only allow me t
make the msg box show and the user would have to click "ok" to make th
msg box disappear...

I really really appreciate your help... especially the speed... it'
amazing...

cheer

 

[Dave Peterson]

Maybe you could just add a label to your existing userform and show the status
there.
(me.repaint might be useful)

Or you could hide the first userform and show the second, then unload the second
and show the first.

Msgbox's won't work for you. Excel is waiting while the message box is shown.
But in my "look at the statusbar msgbox, I wouldn't want it dismissed until it
was read, er, clicked.)

(personally, I'd still use the .statusbar <vbg>.)

 

[hce]

Hi Dave

Thank you very much for your help

cheer