Progress Bar with VBA Macro

[agamemnian]

Hi guys

I was hoping to ask for some advice on creating a progress bar to show the progress of an Excel Macro.

I have a single 'main' macro that calls upon multiple other macros when processing. The first question is would that make a difference to the progress indicator or should it be ok?

I have a userform made of 2 labels and a frame.

I have the following code behind the userform.

-------------------------------------------------

Private Sub UserForm_Click()

End Sub



Private Sub CommandButton2_Click()

UserForm1.Show

End Sub

--------------------------------------------------

In the module I have the following code:

--------------------------------------------------

Private Sub CommandButton2_Click()

UserForm1.Show

End Sub


Sub OpenNGSCFilesProgress()
'
Dim i As Integer, pctCompl As Single
Sheet1.Cells.Clear
For i = 1 To 100
Next
pctCompl = i
progress pctCompl
'
'Open NGSC Stats Matrix Files Macro
'
'Disable Screen Udating and Events
Application.ScreenUpdating = False
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\NGSC TSE Statistics Matrix Template.xlsm"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColC.xlsx"
Application.Run "RawName"
Application.Run "ColC"
Application.Run "NGSCColC"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColE.xlsx"
Application.Run "RawName"
Application.Run "ColE"
Application.Run "NGSCColE"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColFG.xlsx"
Application.Run "RawName"
Application.Run "ColFG"
Application.Run "NGSCColFG"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColH.xlsx"
Application.Run "RawName"
Application.Run "ColH"
Application.Run "NGSCColH"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\Chat.xlsx"
Application.Run "RawName"
Application.Run "Chat"
Application.Run "NGSCChat"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColM.xlsx"
Application.Run "RawName"
Application.Run "ColM"
Application.Run "NGSCColM"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColD.xlsx"
Application.Run "RawName"
Application.Run "ColD"
Application.Run "NGSCColD"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColK.xlsx"
Application.Run "RawName"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\Col J - Closed within FCR - Example.xlsx"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\Col K - Res Supplied with FCR - Example.xlsx"
'
Workbooks.Open Filename:="C:\SCA Stats Matrix\ColJ.xlsx"
Application.Run "RawName"
Application.Run "ColJ"
Application.Run "NGSCColJ"
'
Windows("ColC.xlsx").Close SaveChanges:=True
'
Windows("ColD.xlsx").Close SaveChanges:=True
'
Windows("ColE.xlsx").Close SaveChanges:=True
'
Windows("ColFG.xlsx").Close SaveChanges:=True
'
Windows("ColH.xlsx").Close SaveChanges:=True
'
Windows("ColJ.xlsx").Close SaveChanges:=True
'
Windows("ColK.xlsx").Close SaveChanges:=True
'
Windows("ColM.xlsx").Close SaveChanges:=True
'
Windows("Col J - Closed within FCR - Example.xlsx").Close SaveChanges:=True
'
Windows("Col K - Res Supplied with FCR - Example.xlsx").Close SaveChanges:=True
'
Windows("Chat.xlsx").Close SaveChanges:=True
'
Windows("NGSC TSE Statistics Matrix Template.xlsm").Activate
Application.Run "HideTabsNGSC"
Sheets("All Regions").Select
'
'Enable ScreenUpdating and Events
Application.ScreenUpdating = True
'
End Sub


Sub progress(pctCompl As Single)

UserForm1.Text.Caption = pctCompl & "% Completed"
UserForm1.Bar.Width = pctCompl * 2

DoEvents

End Sub



Private Sub CommandButton2_Click()

UserForm1.Show

End Sub

------------------------------------------------------------------------

This is linked to a command button which has this behind it:

Private Sub CommandButton2_Click()

UserForm1.Show

End Sub

------------------------------------------------------------------------

When I click it no matter what I seem to do the progress indicator immediately jumps to 101% and doesnt move.

Any ideas on what I've missed here? I'm tearing my hair out trying to work this out.

Also when it's completed I want to hide the userform.

Is there a code userform.hide i could use to do this?

Any help would be greatly appreciated.

Thanks

 

[CalcSux78]

There's a beginner tutorial on Progress Bars here: www.excel-easy.com/vba/examples/progress-indicator.html

It looks like you are opening 11 workbooks and running code with each workbook. Before opening the next workbook, you could increase your completion percentage.

 

[agamemnian]

Great. Sorry for the delayed response. Thank you.