Forum Discussion
Jedzrej411
Oct 14, 2022Copper Contributor
Macro status bar
Hi, I have quite long makro which runs for about an hour. I want to add a progress bar or some kind of life status update to see if macro doesnt crush or how much time to end. Unfortunately I don't ...
jedrzej411
Oct 14, 2022Copper Contributor
Thanks for such a prompt reply! As I understand I need to paste this code to any loop in the code? I forgot to add that macro also stops screen updating. Will this bar be visible in such a case?
I attach one of my macro loop, another loops are very similar and have only different folder names.
Public Sub TEST_INV()
Dim A As Integer
Dim B As Integer
Dim X As String
Application.ScreenUpdating = False
For A = 1 To 100
X = "STOCK"
S = Sheet2.Range("A1").Value
d = X & "_" & A
B = ActiveCell.Column
C = B - 1
E = B + 1
ChDir S & X
Workbooks.Open Filename:= _
S & X & "\" & d & ".xlsm"
Sheets("Sheet1").Select
If Sheets("Sheet1").ProtectContents Then Sheets("Sheet1").Unprotect
'Sheets("Sheet1").Unprotect
ActiveWindow.ActivateNext
ActiveCell.FormulaR1C1 = _
"=SUMIF('[" & d & ".xlsm]Sheet1'!C1,RC[-" & C & "],'[" & d & ".xlsm]Sheet1'!C6)"
Selection.AutoFill Destination:=Range(Cells(3, B), Cells(6356, B))
Range(Cells(3, B), Cells(6356, B)).Select
Windows(d & ".xlsm").Activate
ActiveWindow.Close SaveChanges:=False
'ActiveWorkbook.Save
Cells(3, E).Select
Next A
Application.ScreenUpdating = True
HansVogelaar
Oct 14, 2022MVP
If you have separate loops: yes, in every loop.
If you have nested loops: in the deepest loop, or if that is unwieldy, in the next outer one.
The status bar should still be updated when ScreenUpdating is set to False, but depending on what's happening in your loops, it might be intermittent. It helps to add a line
DoEvents
in the loops, but that slows down execution too - you'll have to find a compromise.
- jedrzej411Oct 14, 2022Copper ContributorHans,
I followed your advices and it works, status bar is being updated even after Screen updating on false.
Thanks a lot for your help! 🙂