Remove Contents of a cell based another cell's change...

[dbmagnus]

I want to clear the contents of cell E2 when someone changes the date value in B7. I don't think it can be a Worksheet_Change event, because there will be values changing in other cells in the worksheet while background queries are running. (I received an "Out of Stack Space" error).

Any suggestions?

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B7" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Me.Range("E2").ClearContents
End If

ws_exit:
Application.EnableEvents = True
End Sub

[dbmagnus]

This works great. However, if I put that below the "Next" in the code below, it does the paste before the 30 data queries have completed.

Public Sub refreshtest2()
Dim qt As QueryTable
For Each qt In Worksheets("Data").QueryTables
qt.refresh
Next

End Sub

How do I make VB wait until the last QueryTable has been refreshed before continuing?

[frank_m]

hmmm... :think: you can't really paste the change event inside your Public sub. For one thing the change event is a sheet event in the sheet class module. the other sub has to be in a regular module. You could call your public sub to run from within the change event if that is the requirement, or just add the refresh query part of the code there... Then if that works, at that time it's worth trying adding the command Doevents after the query refresh and before the change cell code.

[Bob Phillips]

You could also disable events in your refresh module, and enable at the end.

[dbmagnus]

I'm not sure if this is hi-jacking my own thread, but the requirements have changed and now instead of one "data" tab with all my QueryTables in it there are 20 tabs, each with their own QueryTable.

How do I alter the VB code to update the QueryTable on each tab?



Public Sub refreshtest2()
Dim qt As QueryTable
For Each qt In Worksheets("Data").QueryTables
qt.refresh
Next


End Sub


Thanks.

[Bob Phillips]

Why not do



ActiveWorkbook.RefreshAll

[dbmagnus]

Thanks, xld. I've since added PivotTables to each worksheet and now your suggestion no longer works. Does "RefreshAll" refresh PivotTables as well as QueryTables?

What if I want to first refresh All QueryTables, then after those have been refreshed I want to refresh All PivotTables?

Thanks.

[Bob Phillips]

It should refresh all objects.