title: Adding a progress bar to macro in excel
body: <p>I have a VERY simple macro that ties a refresh to a button.  All it does is I click the button, and it refreshes all external connections (xml and web) in the workbook.  However, I'm trying to add either a progress bar or a status message in the message bar.</p>
  
  <p>Right now it's simple:</p>
  
  <pre><code>Sub refreshAll()
      ActiveWorkbook.refreshAll
  End Sub
  </code></pre>
  
  <p>It does what it's supposed to, now I just want some way of telling how far along it is (and getting the process to run in the background.  I'd like to change this to something that counts how many connections there are, then goes through and refreshes each connection while counting it out in the status bar.</p>
  
  <p>Such as:  "15 of 300 finished 20%"</p>
  
  <p>And it just simply counts as it goes through them updating the bar as it goes.  I don't know if there is a way to update the bar using the refreshAll, or if i have to loop through and refresh them counting as it goes.  Any ideas?</p>
  
  <p>EDIT:</p>
  
  <p>I have it working for the most part:</p>
  
  <pre><code>Sub refreshAll()
  
      Dim con As String
      Dim cLen As Integer
      Dim i As Integer
      Dim pInt As Double
      Dim percent As String
      cLen = ActiveWorkbook.Connections.count
      i = 0
  
      Do
          con = "Connection" &amp; i
          pInt = i / cLen
          percent = FormatPercent(pInt, 0)
          ActiveWorkbook.Connections(con).Refresh
          Application.StatusBar = "Updated " &amp; i &amp; " of " &amp; cLen &amp; " | " &amp; percent &amp; " Complete"
          i = i + 1
      Loop While i &lt; cLen
      Application.StatusBar = "Update Finished"
  
  End Sub
  </code></pre>
  
  <p>The problem is that everything works as it should, it counts the correct number of connections, and in the status bar it prints like it should but it zips through in a second without actually updating any of the connections.  If I go hit the "Refresh All" button it takes about 15 seconds to refresh the data, if I click the button I created it just zips through without refreshing.</p>