# Off Topic > The Water Cooler >  >  Progress Indicator Bar

## abousetta

Hello,

I have been searching for different options for progress bars/ indicators and I have found a ton, but with variable levels of programming involved. My question for everyone is what is your favorite Progress Bar and why (e.g. ease of use, presenation, efficiency, etc.). If you could upload a dummy workbooks rather than just code, it would be easier for everyone to replicate what you have.

Thanks.

abousetta

----------


## MarvinP

Hey abousetta,
See http://spreadsheetpage.com/index.php...ess_indicator/ and the line of"
Click Here to Download.

That should give you the sample you want.

----------


## Kyle123

I'm a huge fan of this one, I didn't make it so I can't take credit for it and I couldn't find it on google or I would link back to it.

It is flexible, powerful and easy to implement and customise - with an optional cancel button.

Includes time elapsed and time remaining also

If you would like any of it explaining, please let me know

----------


## abousetta

Thanks to the both of you. I had seen John Walkenbach's progress bar before, but I had never seen the one that @Kyle123 proposed. It definitely has a WOW factor... 

Thanks again and hoping to see more posts. I think a lot people are having trouble finding the right one for their individual projects and maybe this can be a good place to start.

abousetta

----------


## Mordred

This is a great thread you started abousetta, I have moved it to the water cooler though as this seems to be the spot for topics like these.

----------


## abousetta

Hi Mordred,

No problem. I wasn't sure if the where to best put this thread and so the Water Cooler is fine with me. I hope to get a good collection so that people have a one-stop place for solutions to this issue.

Thanks.

abousetta

----------


## alansidman

Here's one that I have used.  Can't take credit for building--only for finding it.

http://www.datapigtechnologies.com/f...augechart.html

Alan

----------


## abousetta

I have created a modified progress bar that also calculates the remaining run time for a macro as some of my codes run for hours to days.

Hope it helps others like it has helped me.

Good luck.

abousetta

----------


## Mordred

I like that abousetta.  I've tried to implement a progress bar into one of my query forms but haven't been successful.  I'm going to study yours and see if I have the know-how to apply it.

----------


## abousetta

Hi Modred,

It took me some time to understand the concept. It's kind of simple... 

1) create progress markers (e.g. every cell, every row, every line of code, etc.) and find a way to count them. For example if rows, then how many rows are being used in the worksheet. If different parts of your code then you will have to hard code it.
2) Use a counter to increment each time a task is completed and this should trigger the progressbar update function.

If you increment this correctly then you should reach 100% at the end of the code. So tweak accordingly.

As for the remaining time, I used used a simple variable to measure the time it takes to complete each task and calculate the remaining the time based on this.

Hope this helps.

abousetta

----------


## JosephP

I came upon this article a while ago which I thought was a pretty good primer on progress bars (you could expand it further e.g. to use an interface class): http://www.experts-exchange.com/Soft...soft-Apps.html

note: if you're one of those folks that think Experts Exchange is the 7th circle of hell, or a plague on mankind or some such, just don't click the link. ;-)

----------


## abousetta

Thanks JP. I don't know the story behind the experts-exchange but I guess it would to hear about a campfire  :Smilie:

----------


## npamcpp

Experts Exchange has a very active Excel community and heaps of contributing MVPs. But since the site requires that you pay a fee to be able to ask questions, there are a number of people who have been raging against Experts Exchange for years, especially those who lean towards Stack Overflow (Stack Exchange). 

All articles on Experts Exchange are free to read, and if you register as an expert and answer about three questions a month, you can ask as many questions as you like and browse their impressive solution database. Everyone pays to be able to ask questions. Some with money, others with their time.

----------


## abousetta

Now that I think about it, I have come across the Experts Exchange a couple of times when searching for solutions. Like other sites that require me to pay or register to see an answer, I just move on to the next. No hurt feelings and no resentment. That's what freedom is all about. They have the right to charge and I have the right to refuse to pay. But I didn't know about the option to be registering as an expert. Might look into this.

Thanks for the explanation.

abousetta

----------


## erprasannaa

Hi Kyle,

can you breif how can i include this probgress bar in my macro codes.
Thanks in Advance.



Prasanna.E

----------


## Kyle123

HAve you downloaded the workbook? There's a working example in there, you just increment the bar with every iteration of your loop

----------


## erprasannaa

HI Kyle,

I download the prgress Indicator bar excel and save in my local system.

I run the macro of progress bar it works fine.

I wish to include in my macro .

Here my codes.

I am not a expert in VB.


I am not the owner of these codes.
I take many codes and do some patch work for my conveince.

My macro will do following things.
1.copy and pase special for every sheets.
2.delete 3 lines.
3.put Inr in a cell.
4.some changes and fit for printing.
5.save as pdf and store in a particular dir.

Please help to atttach the progress bar in my codes.

**********************************************************************************************************************************



Sub FreezeAllSheets()

'##################################################################################################

'##################################################################################################
'Below lines for copy  and pase special value for all active workbooks


Dim anySheet As Worksheet
For Each anySheet In ActiveWorkbook.Worksheets
anySheet.Activate
anySheet.UsedRange.Copy
anySheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Next
    Application.ScreenUpdating = False

    Dim wkSt As String
    Dim wkBk As Worksheet
    wkSt = ActiveSheet.Name
    For Each wkBk In ActiveWorkbook.Worksheets
        On Error Resume Next
        wkBk.Activate
'##################################################################################################
         'below 4 lines to delete 3 rows from each sheet and type amount in INR
           Rows("1:3").Select
    Range("B3").Activate
    Application.ScreenUpdating = False

    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp

    Range("C1").Select
     Application.ScreenUpdating = False
    ActiveCell.FormulaR1C1 = "Amount in INR"
    Range("C1").Select






'##################################################################################################
  'below line used for page set up.
   Application.ScreenUpdating = False

  With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
        End With
         Application.ScreenUpdating = False
'##############################################################################################

    'below line for auto fit columns
        Cells.EntireColumn.AutoFit
        ActiveWindow.DisplayGridlines = True
        Application.ScreenUpdating = False

       Next wkBk
'##################################################################################################
'

    Sheets(wkSt).Select
    'Application.ScreenUpdating = false


'##################################################################################################

    'below lines for creating excel work sheets
 Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim sht As Object
    Dim strSavePath As String

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False

    strSavePath = "E:\test\"
    Set wbSource = ActiveWorkbook

    For Each sht In wbSource.Sheets
        sht.Copy
        Set wbDest = ActiveWorkbook
        ''wbDest.SaveAs strSavePath & sht.Name

        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strSavePath & sht.Name, Quality:=xlQualityStandard, IncludeDocProperties:=True _
, IgnorePrintAreas:=False, OpenAfterPublish:=False

     Application.ScreenUpdating = False

        'wbDest.Close
        ActiveWorkbook.Close False


    Next



    Exit Sub

ErrorHandler:
    MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description






End Sub

----------


## Kyle123

Ok, you're going to need to start a new thread. You can provide a link to this one if you like, also be sure to use code tags: 

 Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

----------

