Status Bar indicator while macro running

[mwca]

I have written a macro in Excel, and have turned off the screen updating.
The problem is it takes a long time and ideally I would like to be able to show the progress in the status bar.
I have it saying "Please wait..." but I would really like to show the indicator that fills up with little blue squares (like what comes up if you are saving a big file).

Any help would be very much appreciated...

 

[RamziSaab]

Dont know hwo to get the blue sqaures but with my macros that take a long time to excute i have been able to put the percentage complete in the status bar(i.e.macro that copys sheets for example the status bar would say:

Completed 10 sheets out of 75 (7.5%). Currently Copying (name of sheet)

this however this will depend on what ur macro is doing...might want to consider that instead of the blue sqaures?

 

[mwca]

Thanks for the reply
Percentage complete would be fine I think...
Could you let me know the VBA code you use to show this?

 

[RamziSaab]

Well it depends really on ur code, my one when through loops
so i could just make it do a calc.
e.g.

dim k as integer
dim l as integer
l = workbooks("NAME.XLS&quot.worksheets.count
for each ws in workbooks("Name.xls&quot.worksheets
k = k +1
' Your Code
application.statusbar = "Percentage Complete " & (k/l)*100 & " %"


if u tell me what the macros does i can maybe come up with an idea on how to do it for u

 

[mwca]

actually I think it might be a bit tricky!

There is a row of formulae (row 1, say) which have a common variable (x). Each cell in row 1 relates to a different value of x
The macro sets x to a value, then copies the relevant cell from row 1 and pastes it into the row below as a value. It then sets x to the next value, then copies the next cell from row 1 and pastes into the row below as a value etc...

 

[RamziSaab]

does it go through any loops like
for row = 1 to 100

otherwise u can put it in different parts of the code like

application.statusbar = "Completed 0 %"
'first bit of code
application.statusbar = "Completed 10 %"
' more code
etc.

anychance u can paste the code so i can have a quick look?

 

[mwca]

I think putting it in different parts of the code is the best idea...
Thanks for your help.
The code starts off like this: (but A119 goes up to 100!)

Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Please wait..."

Sheets("Front Page&quot.Select
Range("C31&quot.Select
ActiveCell.FormulaR1C1 = "=Summary!R[88]C[-2]"
Sheets("Summary&quot.Select
Range("A119&quot.Select
ActiveCell.FormulaR1C1 = "0"
Range("C118&quot.Select
Selection.Copy
Range("C119&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A119&quot.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2"
Range("D118&quot.Select
Selection.Copy
Range("D119&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A119&quot.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "4"
Range("E118&quot.Select
Selection.Copy
Range("E119&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A119&quot.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "6"
Range("F118&quot.Select
Selection.Copy
Range("F119&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A119&quot.Select
Application.CutCopyMode = False

 

[RamziSaab]

if u want i can try and rewrite the code for you with the status bar that tells u the percentage as i am sure u can make a loop for what u are trying to do which will go much much faster (but i cant promis miracles)! if the data is confidential u can either change or give me like a sample file (e.g. only 50 lines so i can get an idea of exactly what u want)

email: ramzi_saab@hotmail.com

 

[mwca]

Thanks, but the file is huge (about 10MB!) so it's difficult to email.
The code basically goes on the same as above, but the values for A119 continue going up by 2 (2,4,6,8,10,12,...,100)

I think your other solution should be OK though.
Thanks again

 

[RamziSaab]

hey this is the code i got, but dont understand why u dont copy and past it in the same place so it gets the formula in C118 and paste its in C118 (i left it ur way for now)

Sub newcode()
Dim x As Long
Dim myrows As Long
Dim mycols As Integer
dim c as integer

With Workbooks("Name of workbook&quot.Worksheets("summary&quot

c = Put the number of cols here

x = -2
mycols = 2
For i = 1 To NUMBER OF COLS

mycols = mycols + 1

'sets the value in A119 to 0,2,4 etc
x = x + 2
.Cells(119, 1) = x

'does the copy and paste rountine

Application.StatusBar = "Completed " & (i / c) * 100 & " %)"


.Cells(118, mycols).Copy
.Cells(119, mycols).PasteSpecial Paste:=xlValues
Next i
Application.StatusBar = False
End With
End Sub

tell me if u have problems...this should do exactly what ur one does...there is a statusbar but it goes really fast so i dont think u need one

 

[RamziSaab]

sorry paste this instead

Sub newcode()
Dim x As Long
Dim myrows As Long
Dim mycols As Integer
dim c as integer

With Workbooks("Name of workbook&quot.Worksheets("summary&quot

c = Put the number of cols here

x = -2
mycols = 2
For i = 1 To c

mycols = mycols + 1

'sets the value in A119 to 0,2,4 etc
x = x + 2
.Cells(119, 1) = x

'does the copy and paste rountine

Application.StatusBar = "Completed " & (i / c) * 100 & " %)"


.Cells(118, mycols).Copy
.Cells(119, mycols).PasteSpecial Paste:=xlValues
Next i
Application.StatusBar = False
End With
End Sub
*****************************************************

note:
u have to put the number of columns u want it to do this for where it says :

c =Put the number of cols here (e.g. c = 100)

 

[mwca]

Thanks very much
That seems to work fine!

 

[RamziSaab]

look at this link, just saw in another thread...looks much better then excel default status bar#

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