Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart names in textfile (Excel xp)

    I have this code that puts the names of the charts on the active sheet in a message box. I'd like to put them in excel so i can print them out instead of a message box. Also, is there a way to "name" the charts. I make a lot of charts and may have 50 on one sheet. The chart names may not be in order if i cut some and then make new ones. The chart names could be Chart1, Chart3, Chart4, Chart6, Chart7 etc. I'd like to rename them so they are sequential but haven't been able to figure out where the "name" property of a chart is. Thank you.

    Sub PrintChartNames()
    Dim ChtObj As ChartObject
    Dim Msg As String

    Msg = ""
    For Each ChtObj In ActiveSheet.ChartObjects
    Msg = Msg & ChtObj.Name & vbNewLine
    Next ChtObj
    MsgBox Msg

    End Sub

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,915
    Thanks
    0
    Thanked 193 Times in 176 Posts

    Re: Chart names in textfile (Excel xp)

    Hi jha,

    Here's a simple macro to output all of a Workbook's Chart Names in a column on a worksheet, starting from the selected cell.

    Sub ListCharts()
    If ActiveWorkbook.ActiveSheet.ChartObjects.Count > 0 Then
    For Each Chrt In ActiveWorkbook.ActiveSheet.ChartObjects
    Selection.Formula = Chrt.Name
    Selection.Offset(1, 0).Select
    Next Chrt
    End If
    End Sub

    And here's a generic shape-naming macro that you could use to rename your charts. It uses two arrays - one holding the old names and one holding their corresponding new names. You mention renaming the charts so that the numbers are sequential, but you could just as easily give the charts more meaningful names so that the numbering ceases to be an issue.

    Sub RenameShapes()
    On Error Resume Next
    OldShapes = Array("Chart 1", "Rectangle 2", "Line 3", "Chart 2", "Line 1")
    NewShapes = Array("Trend Chart", "Blue Rectangle", "Connector", "Latest Results Chart", "Underscore")
    For Each OldShp In OldShapes
    For Each NewShp In NewShapes
    With ActiveWorkbook.ActiveSheet.Shapes(OldShp)
    .Select
    .Name = NewShp
    End With
    Next NewShp
    Next OldShp
    End Sub

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    551
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart names in textfile (Excel xp)

    oh, this is soooo cool. i can't wait to try it, this will make a lot of things easier when i'm working on the charts. Thank you very much <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>

Posting Permissions

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