Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Problem with updating Charts Excel 2013

    Hi All,
    I have a project where I am building up some graphs that represent Daily and Monthly production output values. (Lots of others as well, but let's get the first step correct eh?)
    I have created some VBA code that will allow me to choose which Month/Year I want to display on the chart.
    At the moment, I am just trying to get the first graph fully working so there is still more work to go here.
    The problem is that while the data DOES appear to be updating/displaying correctly on the graph, the X axis label, being the Dates, does NOT update.
    I have tried a number of ways to refresh the chart ranges, using Named ranges, using direct long winded absolute coding, Calculated range addresses, but NONE of them force the dates to update to what the correct values should be.
    I thought the idea of using Named Ranges would be good... update the address that the Named Range points to and bingo the graph would update, but nooooo... all that did was stuff up the graph and remove the named range from the top LH corner dropdown box. i.e. I could not select the named range to see if it had updated correctly.
    Strange that I could see the named range in the Name Manager dialog box.
    Current file is attached.
    This is really driving me nuts.
    Can someone point out the bleeding obvious error I have (too stupid to see it myself obviously).
    TIA
    Brian.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Brian

    ..instead of using a Form for the selection, you could use 'sliders' to select the Month and Year.
    And by using formulas to 'get' the relevant data for the Chart, you can avoid the VBA altogether.
    ..see my example attached
    ..just to give you an alternative method

    zeddy
    •Excel Graphic Ratings Assessor
    .
    Attached Files Attached Files

  3. The Following User Says Thank You to zeddy For This Useful Post:

    BRoby (2015-08-19)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Brian

    ..you may have noticed in my example, I used the INDEX function to get the chart data.

    zeddy
    •Excel Index Association

  5. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Hi Zeddy,
    Fantastic solution.
    Like you say, takes out the need to use VBA.
    A solution like this never even entered my thoughts.
    Thanks so much for your assistance.
    Regards
    Brian.

  6. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Hi Zeddy,
    Have tried applying your solution to one of the other graphs that I need to update, along the same lines as the first graph.
    The second graph I have applied this solution to updates the info correctly but the date does not update...
    I have also tried to "blank" out the information/chart data from months that don't have 31 days in them.
    Whilst I have "Blanked" the data in the working area (to the right of the graphs), Excel simply shows the graph hitting 0.
    Is there anyway of avoiding this?
    Can you take a look at my spreadsheet and find out why the date is not updating on the "Daily Contribution" graph?
    I thought initially that the data to graph had to be all in one block, so I copied the date info down to the Daily Contribution data, and pointed the graph data to this area, but this made no difference.
    Thanks
    Brian.
    Attached Files Attached Files

  7. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Brian

    see attached file for a fix.

    Checking the x-axis date range is the same for each of the chart series:
    Chart1: Right-click on the Chart>Select Data
    In left-pane it shows the names of the 3 data series
    Highlight (click) on first entry (i.e. 'Daily Output - ZPPR7'): In right-pane click [Edit] button..

    z-pic1.gif

    change from this..
    z-pic2.gif

    to this.. pic5
    z-pic5.gif

    Highlight (click) second entry (i.e. 'Progressive Daily Output'): In right-pane click [Edit] button..

    z-pic4.gif

    ..make sure date range is same as above. repeat for third series in the left-hand pane i.e. highlight it, and then click the right-hand-pane [Edit] button to make sure the Axis label range is same as previous two.
    Do the same for the other chart 5:

    z-pic6.gif

    zeddy
    •Excel Chart Correspondent
    .
    Attached Files Attached Files

  8. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Brian

    ..in my post#5 file, I dealt with the issue of the months that don't have 31days in them. You were on the right track by 'hiding' the relevant data items (but didn't 'hide' the actual dates).
    I found it easier to 'hide' the entire columns where the days in the month are less than 31.
    I added a named range [checkBlock] and added a small vba routine in module modCharts
    I assigned this routine to the month selection clicker. (right-click the clicker>Assign macro)
    I moved the chart data block for Chart5 to be closer to where the chart is, and I copied the same clickers for month and year, placing them alongside too.

    zeddy
    •Excel Chart Editor
    .

  9. #8
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Hi Zeddy,
    Thanks for your help... again.
    I had not realised that you need to set the X axis Dates for each series.
    This is where I went wrong.
    Also, thanks for the solution to hiding the extra days for months not containing 31 days, like you say, close... but no gold coin this time for me.
    Cheers
    Brian.

  10. The Following User Says Thank You to BRoby For This Useful Post:

    zeddy (2015-08-21)

  11. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Brian

    ..you still get a 'Thanks' from me for your good manners, and politeness, and graciousness, and courtesy, and you get my respect for admitting you learnt something you didn't know before.
    Something we can all learn from.
    I learn an awful lot from this forum too.

    zeddy
    •Deep Sea Pearl Diver
    .

  12. #10
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Hi Zeddy,
    You are very generous. Both with your time and effort on this forum.
    I decided to have another crack at the VBA solution as an exercise in cruelty to myself
    I found that by adding in two lines of code to update the dates for the other columns of data, it worked.
    It was the realisation that I had not updated the date range for each charted value.
    Once again, I was close on the original solution, but I like your solution also.
    Either way, hopefully I can complete the project now.
    Your help has been invaluable.
    Regards
    Brian.

Posting Permissions

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