Page 1 of 2 12 LastLast
Results 1 to 10 of 16

VBA properties of Charts

This is a discussion on VBA properties of Charts within the Excel Questions forums, part of the Question Forums category; Forgive me for I am stumbling through an assignment using charts, so I may misstate or babble here I have ...

  1. #1
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default VBA properties of Charts

    Forgive me for I am stumbling through an assignment using charts, so I may misstate or babble here

    I have a chart that I want a dynamic title on. Thus I believe that I want to plug activechart.charttitle.caption based on input assumptions.

    What I'm wondering is how to identify the chart itself. From the 'Chart Window' (from right clicking an empty area of the chart), I see one possible name in the title bar; from activechart.parent.name in Watch window I see "Chart 1" . So for starters I don't know the name of my only chart!

    At ANY rate Charts.Count is zero! Charts(1) and charts("Chart 1") are rejected by the Watch window.

    Going back to how I started, I guess I'll be okay if I can just know how to make my [sole] chart the "ActiveChart" but beyond that I'm quite clueless. (I don't want to have to manually click the chart to do so either.) Can someone walk me through what I need to do, if this is clear enough?

    TIA
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,948

    Default Re: VBA properties of Charts

    The Charts.Count will return 0 if you don't have any chart "sheets", not embeded charts in worksheets.

    Is that what you have ? embeded charts ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default Re: VBA properties of Charts

    I suppose yes. I used alt-Insert Chart and its wizard to create it atop source data, then cut it and pasted to an empty sheet.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,516

    Default Re: VBA properties of Charts

    If a Chart is embedded in a worksheet it is contained in a ChartObject object. Good isn't it?

    So if you only have one chart on your worksheet you can use:

    ActiveSheet.ChartObjects(1).Chart.ChartTitle.Text = "Whatever"

  5. #5
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default Re: VBA properties of Charts

    Outstanding. My meager ability at least permitted me to go
    sheets("MySheet").activate


    and then your direction worked perfectly.

    Way to go!
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  6. #6
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,673

    Default Re: VBA properties of Charts

    Anti-Bill -

    Quote Originally Posted by Gates Is Antichrist
    I have a chart that I want a dynamic title on. Thus I believe that I want to plug activechart.charttitle.caption based on input assumptions.
    I notice that this group is quick to jump on a VBA approach to things, where a more simple worksheet solution would suffice. Or maybe you're all just better programmers than I am.

    Anyway, couldn't you put the input assumptions into the worksheet somehow, and write a worksheet formula to construct the chart title text within a cell? If you can (and I'm sure you can), then you can link the title to this cell's contents. Make the chart with any title, then select the title, type an equals sign in the formula bar, and click on this cell with the desired title text and press Enter. When the cell's contents change, the title keeps changes too.

    This technique works with chart titles, axis titles, data labels, and after a fashion, with text boxes as well.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    http://www.geocities.com/jonpeltier/Excel/index.html
    _______

  7. #7
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,725

    Default Re: VBA properties of Charts

    Quote Originally Posted by Jon Peltier
    I notice that this group is quick to jump on a VBA approach to things, where a more simple worksheet solution would suffice.
    LOL! That's a lost cause. Leonardo da Vinci would not be very popular around here.

  8. #8
    Board Regular
    Join Date
    Aug 2002
    Posts
    305

    Default Re: Dynamic Title for Chart

    > Make the chart with any title, then select the title, type an equals sign in the formula bar, and click on this cell with the desired title text and press Enter. When the cell's contents change, the title keeps changes too.


    I'm absolutely speechless. I guess the absence of the "refedit" style text box in the Chart properties dialog box would have persuaded most people that such a thing is not possible. (It certainly confounded me!).

    Is this a documented feature, or what?

  9. #9
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,946

    Default Re: VBA properties of Charts

    see the help file for "Link a chart title or text box to a worksheet cell"

  10. #10
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: VBA properties of Charts

    Quote Originally Posted by PaddyD
    see the help file for "Link a chart title or text box to a worksheet cell"
    I agree.

    It's also worth noting that hidden in this thread there are a couple of hyperlinks to two very good sources for numerous Chart tricks and techniques.

    (HINT: both of the authors seem to be fans of "Record Macro")

Page 1 of 2 12 LastLast

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
  •  


DMCA.com