VBA: Query refresh always runs first regardless of where it is in the code.

[joeyslaptop]

I'm trying to create a macro that does the following:

On theButton was clicked
set theButton style =msoShapeStylePreset12
Run the query
Query refresh completes
set theButton style = msoShapeStylePreset14
end

But, the button doesn't change until after the query has refreshed. I eve tried putting it in a loop with a condition:

Sub RefreshQueries()
'
' RefreshQueries Macro
' This macro triggers when the button is pushed.  It refreshes the queries that produce the datasets.

Dim Looper As Integer
    Looper = 1
Do
'change the button to yellow.
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset12
    Looper = 2
    If Looper = 2 Then
'Do query refresh.
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
    Sheets("Mbrs to Renew").Range("H2").ListObject.QueryTable.Refresh BackgroundQuery:=False
    Looper = 3
    End If
Loop Until Looper = 3
'change the button back to green.
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset14
End Sub

 

[joeyslaptop]

Dim Looper As Integer
    Looper = 1
Do
'change the button to yellow.
    If Looper = 1 Then
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset12
    Looper = 2
    End If
    If Looper = 2 Then
'Do query refresh.
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Select
    Sheets("Mbrs to Renew").Range("H2").ListObject.QueryTable.Refresh BackgroundQuery:=False
    Looper = 3
    End If
    If Looper = 3 Then
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset14
    Looper = 4
    End If
Loop Until Looper = 4
'change the button back to green.
End Sub

 

[Fluff]

Add the line in blue as shown

Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset12
[COLOR=#0000ff]DoEvents[/COLOR]

 

[joeyslaptop]

It still doesn't work. I tried commenting out the part where it changes it back to msoShapeStylePreset14. The query refreshes, and then the button turns yellow.

 

[Fluff]

What if you just use

Sub joey()
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset12
    DoEvents
    Sheets("Mbrs to Renew").Range("H2").ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset14
End Sub

How long does it take for the query to refresh?

 

[joeyslaptop]

Same results. The button doesn't change to msoShapeStylePreset12 until after the query refresh completes. And then it switches to mso...14 so quickly - like it won't update the window until after the query refreshes. Is there a way to ensure that a window update/refresh takes place before moving to the next event in the macro?

 

[Fluff]

Could you please answer my question?

 

[joeyslaptop]

Thanks for your help. Here's what worked:

Sub RefreshQueries()
[LEFT][COLOR=#222222][FONT=Verdana]    Application.ScreenUpdating = False[/FONT][/COLOR]
[/LEFT]
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset12
    Application.ScreenUpdating = True 
    Sheets("Mbrs to Renew").Range("H2").ListObject.QueryTable.Refresh BackgroundQuery:=False
    Sheets("Stats").Shapes("Rounded Rectangle 1").ShapeStyle = msoShapeStylePreset14
End Sub

Setting the ScreenUpdate to False and then back to true caused the screen update to activate and update the screen before proceeding to the next step.

 

[Fluff]

Glad you got it sorted & thanks for the feedback

 

[joeyslaptop]

Could you please answer my question?

Sorry - some of your comments came through and some didn't. This site is sort of laggy for me and is crashing both my Edge and IE browsers.