Update Chart during iteration

Hello Everyone,

I am trying to write a macro that will iterate through a specified range of integers. This updates the value of a certain cell to be the value of the current integer, which in turn updates a table of data. A chart displays the data in the table in another worksheet. I need the chart to display the table for every iteration, but it currently does not update until the macro is done. I have tried to find a solution to this, but thanks to VBs help files, I have been unsuccessful so far. Here is the code for what I am trying to do, and some of my attempts to get it to work.

Declare Sub Sleep Lib "kernel32" (ByVal lMilliseconds As Long)
'This enables a timer in milliseconds to be used

Sub ChangeBatch()
    For batch = ActiveSheet.Range("C2:C2") To ActiveSheet.Range("D2:D2") Step 1
    'Gets the values for the min and max of the range

        Worksheets("FDS").Range("Q1:Q1") = batch
        'Updates the cell that the table (and therefore the chart) depends on

        'Worksheets("HPLC Graph").ChartObjects(1).Chart.Refresh
        'My first of many unsuccessful attempts

        'Sheets("HPLC Graph").ChartObjects.Charts(1).Refresh
        'This didn't work either

        Application.CalculateFull
        'I thought this would cause the table to update at least

        'ActiveWorkbook.AciveSheet.Chart(1).Refresh
        'Still nothing

        Sleep ActiveSheet.Range("E2:E2") * 1000
        'Pause for a specified amount of time to enable viewing of the data
    Next
End Sub

And I've tried many, many more ideas, but none have worked. Most fail without even running. I think I'm really close to the solution, I just don't know the correct syntax to get it to work. I renamed the chart that I want to update as "HPLC" through the properties tab under Layout. I don't know if that helps.

Thanks for any help you may be able to provide.

Jon,

1. Do you get compile-time errors, run-time errors, or neither?

2. When you change the input range, does the chart data indeed recalculate?

3. Have to tried code to select the chart (meaning that you can find the chart in code)? That same method, with Refresh replacing Select, should work ...

shg,

On some of those I get compile-time errors, others I get run-time errors. The important thing is that I haven't found the right combination of objects, indeces and functions to access what I need.

The chart is based off of a table of data that looks up another table of data, so it doesn't really recalculate, it just changes where it looks.

I think I've tried to select the chart. I haven't done so with Select, but I think I have tried through other methods. Could you post an example of how I would do that? And could it include more or less a complete path (i.e. WorkBooks("Sheet1").Application.Chart("HPLC").Select or something like that). I don't know the proper syntax for something like that yet.

Thanks

I have verified that the table that the chart is referencing is being updated after each step of the loop. It is just the chart that is not being refreshed. Can anyone post how I can tell all charts in the workbook to refresh after each step of the loop, and how I could tell just one chart to refresh?

Thanks

I don't know if this will help, but I did find out that if I use

Application.Wait Now() + 0.5 / 60 / 60 / 24

instead of

Sleep Worksheets("HPLC Graph").Range("E2:E2") * 1000

the chart will update after each step. Unfortunately (and the reason I used the latter in the first place), I need the resolution of the pause to be in milliseconds. Using Wait, it either runs as fast as it can (if the first number is less than 1) or it runs an integer number of seconds, neither of which are optimal for me.