title: VBA - Show Progress Bar While Macro Executes
body: <p>I have a macro that opens xlsx files in a folder one at a time and copies their sheets into a specific file. Sometimes this macro takes rather long to run and I would like to add a progress bar to show the user how far along the macro is.</p>
  
  <p>I found a few guides that show how to do this, and I tested them in sample workbooks. Now, I am trying to integrate the guides with my macro but I am not having any success. </p>
  
  <p><strong>Here is my code (to copy the sheets)</strong>: </p>
  
  <pre><code>Sub ImportDataSheets()
  
      Dim X As Workbook
      Set X = Workbooks("3rd Party.xlsm")
  
      path = "X:\Test\3rd Party\\"
      Filename = Dir(path &amp; "*.xlsx")
  
      Do While Filename &lt;&gt; ""
          Workbooks.Open Filename:=path &amp; Filename, ReadOnly:=True
  
          For Each Sheet In ActiveWorkbook.Sheets
  
              Sheet.Copy After:=X.Sheets(1)
  
          Next Sheet
  
          Workbooks(Filename).Close
          Filename = Dir()
  
      Loop
  
  End Sub
  </code></pre>
  
  <p>Here is the link to the guide for using a form as a progress bar:</p>
  
  <p><a href="http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/" rel="nofollow noreferrer">http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/</a></p>
  
  <p>Here is the basic breakdown of that guide:</p>
  
  <p>1) Insert form and make it look like this:</p>
  
  <p><a href="https://i.stack.imgur.com/nel0L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nel0L.png" alt="enter image description here"></a></p>
  
  <p>Added a frame (renamed to FrameProgress) inside the form and a label (renamed to LabelProgress) inside the frame</p>
  
  <p>2) Right click on the form and click on view code</p>
  
  <p>3) Inside the window, add this code:</p>
  
  <pre><code>Private Sub UserForm_activate()
      Call Main
  End Sub
  </code></pre>
  
  <p>4) Insert a module and add this code:</p>
  
  <pre><code>Sub Main()
  '   Inserts random numbers on the active worksheet
      Dim Counter As Integer
      Dim RowMax As Integer, ColMax As Integer
      Dim r As Integer, c As Integer
      Dim PctDone As Single
  
      If TypeName(ActiveSheet) &lt;&gt; "Worksheet" Then Exit Sub
      Cells.Clear
      Application.ScreenUpdating = False
      Counter = 1
      RowMax = 100
      ColMax = 25
      For r = 1 To RowMax
          For c = 1 To ColMax
              Cells(r, c) = Int(Rnd * 1000)
              Counter = Counter + 1
          Next c
          PctDone = Counter / (RowMax * ColMax)
          With UserForm1
              .FrameProgress.Caption = Format(PctDone, "0%")
              .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
          End With
  '       The DoEvents statement is responsible for the form updating
          DoEvents
      Next r
      Unload UserForm1
  End Sub
  </code></pre>
  
  <p>5) Insert a module and add this code:</p>
  
  <pre><code>Sub ShowDialog()
      UserForm1.LabelProgress.Width = 0
      UserForm1.Show
  End Sub
  </code></pre>
  
  <p>6) Run the "ShowDialog" module and it will fill data from cell A1 - cell Y100 and display a progress bar while doing so - This works 100%</p>
  
  <p>I noticed in the above code, there's a counter and that counter gets used to divide by the row and column count combined to get the percentage, so I got the below code to do a count of the files in the folder so that I would have a counter value - and after every file gets closed, the second count variable would increment by 1.</p>
  
  <p>Here is where I got the code for the counter:</p>
  
  <p><a href="https://stackoverflow.com/questions/16753701/count-files-in-specific-folder-and-display-the-number-into-1-cel">count files in specific folder and display the number into 1 cel</a></p>
  
  <p>Code:</p>
  
  <pre><code>Sub sample()
  
      Dim FolderPath As String, path As String, count As Integer
      FolderPath = "X:\Test\3rd Party"
  
      path = FolderPath &amp; "\*.xlsx"
  
      Filename = Dir(path)
  
      Do While Filename &lt;&gt; ""
         count = count + 1
          Filename = Dir()
      Loop
  
      Range("Q8").Value = count
      'MsgBox count &amp; " : files found in folder"
  End Sub
  </code></pre>
  
  <p>Now here is where and/how I have tried to "combine" my code with the guide:</p>
  
  <p>1) This is what the code in my form looks like:</p>
  
  <pre><code>Sub UserForm_activate()
      Call testing
  End Sub
  </code></pre>
  
  <p>2) This is what my sub looked like:</p>
  
  <pre><code>Sub testing()
  
      Dim FolderPath As String, path As String, count As Integer
      Dim PctDone As Single
      Dim Counter As Integer
      FolderPath = "X:\Test\3rd Party"
  
      path = FolderPath &amp; "\*.xlsx"
  
      Dim X As Workbook
      Set X = Workbooks("3rd Party.xlsm")
  
      Counter = 1
  
      Filename = Dir(path)
  
      For r = 1 To count
  
          Do While Filename &lt;&gt; ""
  
              Workbooks.Open Filename:=path &amp; Filename, ReadOnly:=True
  
              For Each Sheet In ActiveWorkbook.Sheets
  
                  Sheet.Copy After:=X.Sheets(1)
  
                  Workbooks(Filename).Close
  
                  Filename = Dir()
  
              Next Sheet
  
              count = count + 1
  
          Loop
  
          PctDone = Counter / count
  
          With UserForm1
  
              .FrameProgress.Caption = Format(PctDone, "0%")
              .LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
  
          End With
  
          DoEvents
  
      Next r
  
      Unload UserForm1
  
  End Sub
  </code></pre>
  
  <p>I have many macros, and it would be fantastic to use this with the ones that take long to execute, so I am hoping that if I get it to work with one, I can use it with them all.</p>