Waiting for Refresh

[Abelethan]

I am trying to get a macro to run when a cell is selected. After a parameter is entered into the cell above it for a Query. My problem is the On_Refresh routine runs before the refresh is finished..I have turned off the "Enable backgorund refresh" in the properties of the query. I have tried adding Application.Wait times to allow the query to run first. That hasn't worked either. The query is fast when it runs by itself <1sec but can't seem to get it to update the table before the macro is taking the table values and plugging them into the other worksheets cells. Any help would be appreciated.

Thanks,
Ryan

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)    BackgroundQuery = False
    If Target.Address = "$G$11" Then
    Call On_Refresh
    End If
End Sub

Sub On_Refresh()

BackgroundQuery = False
Worksheets("QMS-IQA-F-7-4-005 C").Select
Worksheets("AutoPrint").Select
Application.Wait (Now + TimeValue("00:00:01"))
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("C4").Value = Date
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("C5").Value = ThisWorkbook.Worksheets("AutoPrint").Range("G5").Value
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("C6").Value = ThisWorkbook.Worksheets("AutoPrint").Range("F21").Value
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("C7").Value = ThisWorkbook.Worksheets("AutoPrint").Range("H21").Value
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("D7").Value = ThisWorkbook.Worksheets("AutoPrint").Range("I21").Value
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("C8").Value = ThisWorkbook.Worksheets("AutoPrint").Range("G21").Value
ThisWorkbook.Worksheets("QMS-IQA-F-7-4-005 C").Range("C9").Value = ThisWorkbook.Worksheets("AutoPrint").Range("J21").Value


Worksheets("QMS-IQA-F-7-4-005 C").Select
ActiveSheet.PrintOut
End Sub

 

[jsotola]

this may or may not work, i'm guessing on this

i assume that the query you mentioned is actually a pivot table update

get rid of the "call on_refresh" in Worksheet_SelectionChange


put your on_refresh code into Worksheet_PivotTableUpdate

once the pivot table finishes updating, the code will run

optionally check which pivot table updated (myWatchedPivotTable is pseudo code, correct value needs to be substituted)

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
      
      if Target <> myWatchedPivotTable then exit sub      ' exit if this is not the pivot table that i wait for
      
      if Target.Name <> "myPivotTable" then exit sub      ' maybe this?

      '  original code here

      :
      :
      :

 

[Abelethan]

No..Its not a pivot table. Its a SQL Query that is pulling data from our main database.

 

[jsotola]

set a global variable

dim sqlRunning as boolean

sqlRunning = false

------------------------------------------
your sql code ... something like this


sqlRunning = true

run sql

sql done

sqlRunning = false

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

your macro ... add this

while sqlRunning      ' keeps looping until sql code is done
    doEvents
loop

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

how is the sql code running at the same time as your macro?

is one of them event driven?