Progress Bar

[sepandb]

I have a few different macros (ImportData, HideColumns etc.) that I want to display a progress bar for when those macros are run. I've read many different ways to do this when I search it on google but I would prefer to use the cleanest method where I can simply call the appropriate procedure for each different macro, without changing any of the other code of the progress bar. Any suggestions would be greatly appreciated.

 

[VoG]

Take a look here http://j-walk.com/ss/excel/files/developer.htm

 

[sepandb]

How can I get the percentage to update accordingly?

 

[sepandb]

Are progress bars only possible when you have a loop? Like is it impossible to make one for a procedure that simply hides the empty columns on my spreadsheet?

 

[VoG]

Why do you need a progress bar for that? The code should be very fast.

 

[sepandb]

Why do you need a progress bar for that? The code should be very fast.

Its actually quite inconsistent and I am unsure why. Sometimes it is instantaneous, other times it takes 3-4 seconds. Also I have a macro where I update my headers/footers by copying cells from excel and that takes 3-4 seconds also and I would want progress bars for both.

This is the code for updating my headers/footers. Where can I update my progress bar given the following code? From what I understand, the code to update the progress bar/percentage complete needs to be embedded within the code of the actual procedure.

Sub CommandButton1_Click()
'Adding Headers/Footers to Control Valves, Shutoff Valves, SOV and Spare Parts

Application.ScreenUpdating = False

LastModified = Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "mm/d/yyyy")


Sheet3.PageSetup.CenterHeader = Sheet2.Range("M4").Value & Chr(13) & Sheet2.Range("M5")
Sheet3.PageSetup.RightHeader = "Quotation #: " & Sheet2.Range("M6").Value & Chr(13) & "Rev. " & Sheet2.Range("M7").Value & ""
Sheet3.PageSetup.LeftFooter = "Quote Date: " & Sheet2.Range("M8") & Chr(13) & _
                             "Revision Date: " & LastModified
Application.ScreenUpdating = True
End Sub

 

[VoG]

You could do something like this (assumes you have added John Walkenbach's code).

Sub CommandButton1_Click()
'Adding Headers/Footers to Control Valves, Shutoff Valves, SOV and Spare Parts
Dim sb As clsProgressBar
Set sb = New clsProgressBar ' create a new progress bar
sb.Show "Please wait", vbNullString, 0 ' display the progress bar
Application.ScreenUpdating = False

LastModified = Format(ThisWorkbook.BuiltinDocumentProperties("Last Save Time"), "mm/d/yyyy")
sb.PercentComplete = 25
Sheet3.PageSetup.CenterHeader = Sheet2.Range("M4").Value & Chr(13) & Sheet2.Range("M5")
sb.PercentComplete = 50
Sheet3.PageSetup.RightHeader = "Quotation #: " & Sheet2.Range("M6").Value & Chr(13) & "Rev. " & Sheet2.Range("M7").Value & ""
sb.PercentComplete = 75
Sheet3.PageSetup.LeftFooter = "Quote Date: " & Sheet2.Range("M8") & Chr(13) & _
                            "Revision Date: " & LastModified
sb.PercentComplete = 100
Application.ScreenUpdating = True
Set sb = Nothing ' remove the progress bar
End Sub

 

[Tom Urtis]

Here is a downloadable example that might help you:

http://www.atlaspm.com/faqvbaprogress.html#58

 

[sepandb]

I get a "compile error: User-defined type not defined" at the line "Dim sb As clsProgressBar"

I have added Walkenbach's code. Thanks for your help

 

[VoG]

With John Walkenbach's workbook open, go into the VBE, in the Project window open up Class Modules and double click clsProgressBar. File > Export and save somewhere handy. Then in your workbook, open the VBE, File > Import and import clsProgressBar.

Your code should now compile.