Macro command to refresh after cell value changes

[vinsis300]

Hi guys, new to the forums. Been searching the web for hours to find a way to refresh web queries for only the cells that have changed values. I created several sheets to run queries and have them set to update when the values on my main sheet change for particular cells. In my macro, everything runs fine to the point where I need the macro to refresh and I have tried ThisWorkbook.RefreshAll but there are 50 worksheets and not all of them need to be refreshed. Is there basic code to have the refresh only for the cells that have changed values? When I stop the macro at any point, the cells that have new values refresh their respective queries, but during the macro it won't do them.

 

[Teeroy]

Hi and welcome to the forum.

How are the webquery updates being run? Are they with Work_sheet change events? If so they should still work during the macro run unless you have Application.EnableEvents set to False.

 

[vinsis300]

Hi and welcome to the forum.

How are the webquery updates being run? Are they with Work_sheet change events? If so they should still work during the macro run unless you have Application.EnableEvents set to False.

Hi Teeroy. Thanks for the quick response. Pardon my lack of vba knowledge in my responses. The queries I am running were created to web query from MSN and yahoo and to refresh when the values change in a particle cell on my main sheet. Is there a way in my code that I need to enable events as true to have the queries run? I think that may be a start as they aren't running right now. I don't have code written for a URL query. The queries are attached to each separate sheet for different tickers.

 

[Teeroy]

It sounds like you have made a dynamic web query where you pass a cell value to an iqy file. I'm not sure how these are treated during macro run-time but you can easily create a refresh based on a change in that cell value using a worksheet_change VBA event such as below:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$M$1"
    QueryTables("YourQueryName").Refresh
Case "$N$1"
    QueryTables("YourQueryName2").Refresh
End Select
End Sub

This must be stored in the Sheet Code module of the sheet that has the cell that changes.

In the above example if the value in cell M1 changes then "YourQueryName" is refreshed and if the value in cell N1 changes then "YourQueryName2" is refreshed. You can have any number of cells in the one sheet in the Select Case block.

Hope this helps.

 

[vinsis300]

It sounds like you have made a dynamic web query where you pass a cell value to an iqy file. I'm not sure how these are treated during macro run-time but you can easily create a refresh based on a change in that cell value using a worksheet_change VBA event such as below:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$M$1"
    QueryTables("YourQueryName").Refresh
Case "$N$1"
    QueryTables("YourQueryName2").Refresh
End Select
End Sub

This must be stored in the Sheet Code module of the sheet that has the cell that changes.

In the above example if the value in cell M1 changes then "YourQueryName" is refreshed and if the value in cell N1 changes then "YourQueryName2" is refreshed. You can have any number of cells in the one sheet in the Select Case block.

Hope this helps.

Thanks Teeroy. I will give that a try tonight. Do I need to set Application.EnableEvents set to True?

 

[vinsis300]

Teeroy. I do have a dynamic query for the web queries. the code above, is that the same as for when a cell changes within a macro. How do I incorporate that sub macro within my main macro to check and see if the value has changed?

 

[Teeroy]

You are right that the code is event driven and therefore it must be in the Sheet Code Module, and not your Standard Code Module. If you have Application.EnableEvents set to True then the refresh will occur without you incorporating it directly into your macro.

 

[vinsis300]

You are right that the code is event driven and therefore it must be in the Sheet Code Module, and not your Standard Code Module. If you have Application.EnableEvents set to True then the refresh will occur without you incorporating it directly into your macro.

Here is the code I have written for the refresh. The main macro does jump to the private sub when the values change, but the refresh is not occurring. I add the application enable events in the marco below, but not sure if that's how it should be.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = True
    Select Case Target.Address
    Case "$A$2"
        QueryTables("MSN query_1").refresh
    Case "$A$2"
        QueryTables("yahoo risk 2").refresh
    End Select
End Sub

 

[Teeroy]

The Application.EnableEvents needs to be at the beginning of your main module, not the event module. If the events aren't enabled then the event driven module can't be called.

Also you can't have two cases in the select for the same cell address; the first one that satisfies the condition will run then you will exit the Select Block.

 

[vinsis300]

The Application.EnableEvents needs to be at the beginning of your main module, not the event module. If the events aren't enabled then the event driven module can't be called.

Also you can't have two cases in the select for the same cell address; the first one that satisfies the condition will run then you will exit the Select Block.

Gotcha. Is there a way to run multiple queries when a single cell changes? Like to run those two queries ranges on a ce change of A2?