title: VBA for excel running Macros
body: <p>I have a macro running sheets in excel where the data being shown is connected to an external database.  I am trying to have the data that is being shown refreshed as the data source is updated, all while the macro is running.  To be clear, by "running" I mean the macro is displaying each sheet in the workbook for a number of seconds and just looping. </p>
  
  <p>Here is the code:</p>
  
  <pre><code>Option Explicit
  Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As             
  Long) As Integer 
  
  
  Public Sub Switch()
  Dim ws As Worksheet 
  
      Do
        For Each ws In ThisWorkbook.Worksheets
          ws.Activate
          Application.Wait Now() + TimeValue("00:00:05")
          If GetAsyncKeyState(vbKeyShift) Then Exit Sub
          DoEvents
        Next ws
      Loop
  
  End Sub
  </code></pre>
  
  <p>I have tried to insert ActiveWorkbook.RefreshAll just before 'Next ws' and I have also tried ThisWorkbook.RefreshAll in the same location.</p>
  
  <p>As of now, when I am told the data has been updated, I stop the macro from running, which lets the data refresh, then I run the macro again.  This is what I am trying to avoid having to do.  </p>
  
  <p>Thank you in advance for your help.</p>
  
  <p>Thanks,</p>
  
  <p>Derek </p>