Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change chart type with vba? (OFF2k/WinXP)

    I'm looking to create a toggle button that changes a chart's "type" between XY Scatter and XY Scatter with Smoothed Lines...

    I don't know, however, what the name of my chart is in VBA... and cannot dig up any "properties" dialog when the chart is selected in design view..

    any advise? thanks!

    ..dane

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change chart type with vba? (OFF2k/WinXP)

    Charts on a worksheet belong to the ChartObjects collection of the worksheet. If there is only one chart on the worksheet, you can refer to it as

    Worksheets("sheetname").ChartObjects(1)

    To set the chart type, use

    With Worksheets("sheetname").ChartObjects(1).Chart
    If .ChartType = xlXYScatterSmooth Then
    .ChartType = xlXYScatterLines
    Else
    .ChartType = xlXYScatterSmooth
    End If
    End With

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change chart type with vba? (OFF2k/WinXP)

    Hans,

    as usual, I am ever grateful!

    let's up the ante a bit -- I actually have two charts. I imagine I can simply access each chart by it's "title" field, right? Is there a better way so that if I chance the title of the chart, I don't break my button?

    thanks!
    ..dane

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change chart type with vba? (OFF2k/WinXP)

    No, you cannot refer to a chart by its title directly. Each chart has a unique name; it is assigned by Excel when you create a chart; the first one is - surprise - "Chart 1". This name won't change unless you do so in VBA. So you can use ActiveSheet.ChartObjects("Chart 2") to refer to the second chart.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change chart type with vba? (OFF2k/WinXP)

    Hans, again -- thanks for the quick feedback.

    I will use VBA to change the chart names to better identifiers, and then remove that portion of the code so they'll keep their "new" names. That'll work just fine for me, and the code will have more meaning (more readable).

    thanks again,
    ..dane

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change chart type with vba? (OFF2k/WinXP)

    You can change the chart name in Excel - without VBA. I do this all the time since I usually manually create a chart which I'll later manipulate via VBA. It's eaiser in the code to identify it with a name than index value.

    The trick is in how you select the chart. If you select it with the shift key you can then change its name (from "Chart 1") to whatever you want in the name box.

    See the attached .xls file for sample screen shots.

    Deb

Posting Permissions

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