Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Changing the X scale of a chart

    I want to have a chart's X scale maximum automatically change to "now" plus 2 weeks when I open a Workbook.

    I can do this in VBA (once I had realised that VBA uses the US convention for dates !) of which an extract is:

    Code:
    With ActiveChart.Axes(xlCategory)
            .MinimumScale = "01/01/2010"
            .MaximumScale = "12/20/2013"
    I don't know an easy way to create the required string: "12/20/2013".

    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Format(Date + 14, "mm/dd/yyyy")
    perhaps?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you Rory - just the hint I needed !

    In passing, the date format thing is a pain - I recorded a Macro to get a rough idea of the code I needed to set the X axis parameters and it refused to run. Typing in 27/10/2010 (for 27 October 2010) in the Chart dialog worked fine but the recorded macro treats this as (tries to treat this as) the 10th day of the 27th month. Its the first instance I have found of a simple recorded macro getting confused like this.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    VBA and dates are one of the banes of my life. Fine if you're in the US but a pita otherwise! (especially with filters and pivottables)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I worked recently for a US company which had switched to A4 paper, so maybe the tide is turning !

    On the other hand, years ago I worked, in Milwaukee Wisconsin, for an engineering company which was "switching to metric", by which they meant measuring components in "inches and hundredths of inches".

Posting Permissions

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