Progress bar on running macro

  • Can anybody give me sample on how to incorporate a progress bar on my existing macro. I manage to get a source code in diplaying progress bar but i can only run the progress bar macro before or after my existing macro. I wanted to run the progress bar macro while the other macro is running. Is it possible? can anybody help me?

  • Hi rhaykho


    Welcome to ozgrid.com


    Without seeing your code, you question is impossible to answer. Anyway, one of our resident Excel gurus (Andy Pope) has posted some really cool code.Progress bars

  • My preferred method is to use Excel's application bar to give the user a message. I also change the cursor to show the Hourglass. I would also suggest that you switch off screen updating to prevent the screen flickering & to speed up your macro a little.Try adding this code:


  • Re: Progress bar on running macro


    Hello people


    I have got some code in my application that basically opens a 'Master File' then saves the masterfile with a new name file1, file2 for example.


    the code itself works fine but I want a progress bar to be shown.


    I've been looking at Andy's examples but don't know how to encorporate it with my code.


    I attached a small example of my files to show what I want. Open up MyApplication first.


    Is it possible to have a progress bar while the code is running.


    This is for an end of week routine that would need to run several modules. Can you have the bar displayed if running more that one module.


    Hope all this makes sense.
    Cheers


    Kev.

  • Re: Progress bar on running macro


    Hi Ktrasler


    Progress bar code requires it to be placed inside of a Loop. If your code has no looping done then the Progress bar will not be usable.


    You could display a static message to the user though, download the example below


    DisplayWaitMessage.zip

  • Re: Progress bar on running macro


    Hi,


    As Dave points out you need to have the progress code within a loop.
    Try moving the CreateTeamsheet code inside the Go button event[vba]Sub Progress()
    '
    ' Progress Bar
    '
    Dim lngIndex As Long
    Dim sngPercent As Single
    Dim intMax As Integer
    Dim TEAMSHEETFILENAME As String
    Dim lngTotal As Long
    Dim iCOL As Integer
    Dim iROW As Integer
    Dim x

    Workbooks.Open Filename:="C:\MASTERFILE.xls"

    iCOL = 1
    iROW = 1

    Workbooks.Open Filename:="C:\MASTERFILE.xls"

    With Workbooks("MYAPPLICATION.xls").Sheets("Sheet1")
    lngTotal = .Range("A1").End(xlDown).Row + .Range("B1").End(xlDown).Row + .Range("C1").End(xlDown).Row
    For x = 1 To 3
    Do
    TEAMSHEETFILENAME = .Cells(iROW, iCOL)
    If TEAMSHEETFILENAME = "" Then Exit Do
    Sheets("Sheet1").Range("B1") = TEAMSHEETFILENAME
    ActiveWorkbook.SaveAs "C:\" & TEAMSHEETFILENAME & ".xls"
    lngIndex = lngIndex + 1
    sngPercent = lngIndex / lngTotal
    ProgressStyle1 sngPercent, True
    DoEvents
    iROW = iROW + 1
    Loop
    iROW = 1
    iCOL = iCOL + 1
    Next x
    End With
    ActiveWorkbook.Close


    End Sub[/vba]

  • Re: Progress bar on running macro


    Hi Dave/Andy


    Thanks for your replies


    Dave,


    I have used the DisplayWaitMessage quite a lot and i find it comes in very handy with fairly short code. I wanted to use a progress bar for this as the code will take a long time to run and I wanted to show how far and how long the code had left to run.


    All of the modules that i would need to run for my end of week process use loops so I hope this will be achievable?


    Andy,


    I have put you code into my file, but nothing happens to the bar at all now it stays at 0% while all the code runs.


    Am I missing something very simple?



    Cheers again


    Kev.

  • Re: Progress bar on running macro


    Hi,


    The way lngTotal is calculated fails due to column B only having 1 entry. This causes the total to be 65539 instead of 6. and even 6 as a percentage of 65K is 0.
    This modification should sort you.
    [vba]Sub Progress()
    '
    ' Progress Bar
    '
    Dim lngIndex As Long
    Dim sngPercent As Single
    Dim intMax As Integer
    Dim TEAMSHEETFILENAME As String
    Dim lngTotal As Long
    Dim iCOL As Integer
    Dim iROW As Integer
    Dim x

    iCOL = 1
    iROW = 1

    Workbooks.Open Filename:="C:\temp\MASTERFILE.xls"

    With Workbooks("MYAPPLICATION.xls").Sheets("Sheet1")
    If .Range("A2") <> "" Then
    lngTotal = .Range("a1").End(xlDown).Row
    Else
    lngTotal = 1
    End If
    If .Range("B2") <> "" Then
    lngTotal = lngTotal + .Range("B1").End(xlDown).Row
    Else
    lngTotal = lngTotal + 1
    End If
    If .Range("c2") <> "" Then
    lngTotal = lngTotal + .Range("C1").End(xlDown).Row
    Else
    lngTotal = lngTotal + 1
    End If

    For x = 1 To 3
    Do
    TEAMSHEETFILENAME = .Cells(iROW, iCOL)
    If TEAMSHEETFILENAME = "" Then Exit Do
    Sheets("Sheet1").Range("B1") = TEAMSHEETFILENAME
    ActiveWorkbook.SaveAs "C:\temp\" & TEAMSHEETFILENAME & ".xls"
    lngIndex = lngIndex + 1
    sngPercent = lngIndex / lngTotal
    ProgressStyle1 sngPercent, True
    DoEvents
    iROW = iROW + 1
    Loop
    iROW = 1
    iCOL = iCOL + 1
    Next x
    End With
    ActiveWorkbook.Close

    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!