Results 1 to 6 of 6

Thread: Chart will not update using Macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2007
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Chart will not update using Macro

    Hi all,

    I have a chart that is linked to dataset B (20 rows, 4 columns), that is in turn linked to the larger dataset A (252 rows). I have written a simple macro (see below) that increases the topleft value of dataset B with 1, which has an effect similar to scrolling through dataset A (if that makes sense). The effect would be a dynamic chart, showing the movement of the data in dataset A.

    When I manually increase the topleft value of dataset B, the values are updated and the chart also updates. However, when I try doing it using the macro, the chart will not update. Note that I have added a 'deceleration' mechanism to the code as well.

    Any ideas?

    Thanks in advance.


    Albert

    Code snip:
    For x = 1 To 252

    'Decelerator
    For y = 1 To 5000000
    Next y

    Range("g2").Value = Range("g2") + 1

    Application.Calculate

    Next x

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,675
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Chart will not update using Macro

    do you need to force calculate 252 times, could you do that once after the loop, and maybe work in a refreshall
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    Board Regular
    Join Date
    Mar 2007
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Chart will not update using Macro

    Hi,

    No, not necessarily. That was just an experiment to see if it would work then. But it doesn't with or without.
    Last edited by aboons; May 17th, 2019 at 10:06 AM.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,188
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Chart will not update using Macro

    Try this method
    Place in a NEW standard module and amend sheet name if required
    Test by running macro Test

    Code:
    Private cel As Range
    
    Sub test()
        Set cel = Sheets("Sheet1").Range("G2")
        cel.Value = 0
        Call Delay
    End Sub
    
    Private Sub Update_Chart()
        If cel > 252 Then Exit Sub
        cel = cel + 1
        Call Delay
    End Sub
    
    Private Sub Delay()
        Application.OnTime Now + TimeValue("00:00:01"), "Update_Chart"
    End Sub
    Last edited by Yongle; May 17th, 2019 at 10:30 AM.

  5. #5
    Board Regular
    Join Date
    Mar 2007
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Chart will not update using Macro

    That works. Thanks! Would you care to explain why my method did not work? (but don't bother if you think it's not worth it)

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,188
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Chart will not update using Macro

    Would you care to explain why my method did not work? (but don't bother if you think it's not worth it)
    I do not pretend to understand what goes on under the Microsoft hood, but I have observed that objects like charts etc do not appear to refresh until a procedure ENDS (regardless of anything I have tried)
    Your procedure ends ONCE and the chart is then refreshed
    With my method, the procedure is called repeatedly, ends after each increment thus allowing the chart to be refreshed
    Last edited by Yongle; May 17th, 2019 at 12:05 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •