• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do we know Macro running status - Progress Bar Method

Gunasekaran

Gunasekaran

Member
I am new to VBA sessions. The current task involves opening over 280+ Excel workbooks and validating a few data within each workbook, then saving and closing as a new name for the workbooks. It takes about 30 to 35 minutes. We were unable to touch anything in the system until that time.


How do we see this progress status? It would be helpful if someone could guide or help me to create a very simple running progress bar while Macro running this time...
81037
 
If you can quantify - calculate - the process percentage progression then you can use an UserForm as a progress bar​
(common to find on web) or more easier just indicate the percentage within the status bar like in this thread :​

How to apply "ProgressMeter" Macro

I am having trouble applying the macro "ProgressMeter", it gives a progress bar in the status bar broken up over 10 time intervals, I would like to use it on some of my long macros or series of macros the problem is how to apply it, if I use it like this Sub XXX() Call ProgressMeter Call...
chandoo.org
​
 
Hi Marc, really little difficult to understanding this User form and code. i attached my Macro and tried this code, but something i made mistake, can you support me. this few code i considered from other Blog... Please make me the right code and the right progress bar.


Code: 
Sub Not_Marc_Code()
Dim BarWidth As Long
'launch the ProgressBar userform
frmProgressForm.Show
'shut off all screen updates
Application.ScreenUpdating = False
'loop through all your worksheets
For i = 1 To Worksheets.Count
    With frmProgressBar
        'calculate the bar width for each iteration. The loop number times the overall bar width (200 pixels) divided by the number of loops.
        BarWidth = (i * 200) / Worksheets.Count
        'set the bar width
        .FrameProrgress.Width = BarWidth
        'since the full bar is 200 pixels, the percentage is always 1/2 the barwidth.  In case of odd numbers of loops, I make it show as an integer, rather than decimal.
        .lblStatus.Caption = Int(BarWidth / 2) & " % Progress: "
        'Repaint will force updates to the form, reguardless of ScreenUpdating being false.
        .Repaint
    End With
   
'do your other stuff here
   
Call Consolidate
   
   
Next
'show the screen
Application.DisplayAlerts = True
'unload the form
Unload frmProgressForm

End Sub
 

Attachments

  • ETB_Script_file Final - Community.xlsm
    868.7 KB · Views: 11
Last edited by a moderator:
As you forgot to indicate how to calculate so what is your own percentage formula ?​
As according to very not my code - the one you choosed may slow down the execution - in the previous post​
works according to the number of sheets from a single workbook !​
​
 
According to this information, this is my code. Here is how to put Progress. Ignore my previous code above. I am confused by the code in your blog and other blogs.

Please post a progress bar to this code. This code will perform more than 250+ actions (Loop Condition).


Code: 
Sub Consolidate()
Dim FolderPath As String, Filepath As String, FileName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("ETB").Rows("2:" & Rows.Count).ClearContents

Set Trg = ThisWorkbook.Sheets("ETB")


FolderPath = "C:\ETB\Target\"

Filepath = FolderPath & "*.xls*"


FileName = Dir(Filepath)

Dim lastRow As Long, lastcolumn As Long

Do While FileName <> ""
Set es = Workbooks.Open(FolderPath & FileName)

es.Sheets(1).AutoFilterMode = False
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Range("A2:L" & lastRow).Copy
Trg.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial

es.Close SaveChanges = False

Set es = Nothing
Set File = Nothing

Application.CutCopyMode = False
'On Error GoTo 0
FileName = Dir

Loop

    Set sht = ActiveSheet
    lastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
    Set rng = sht.Range("A2:A" & lastRow)
 
    rng.NumberFormat = "@"
    rng = Application.Text(rng.Value, "000")

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 
The question is : a percentage of what ?​
'cause before any iteration you must know the 'operations' total # in order to calculate this percentage.​
​
If you do not know the total so the easy way to show a 'progress' is to just use the status bar to indicate the counter (of what)​
or a text so without the need of any UserForm ...​
​
 
My percentage is 200. Finally what am expected. I won't get it Here. What others says. I will go and accept that...
 
As I still don't know what is that / from where it comes so just use this 200 value in the post #3 For codeline as the end value ...​
​
 
You must log in or register to reply here.
Back
Top