Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Renaming charts (2003)

    I have a spreadsheet with 15 charts and plan to add 15 more, but want the chart names to be in sequential order (Chart 1024, Chart1025, etc). The new charts start with the "wrong" name - Chart1045 instead of Chart1039 - is there an easy (using VBA is OK) to rename the charts?

    Thanks!!!

    Larry

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

    Re: Renaming charts (2003)

    What is the 'sequential order'? Left to right, then top to bottom, or ...?

  3. #3
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Renaming charts (2003)

    Top to bottom - I've written VBA code to automatically populate the charts, calculate control limits, etc and then print them 3 to a page (top to bottom).

    Larry

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

    Re: Renaming charts (2003)

    Here is a macro:

    Sub RenameCharts()
    Dim cht As ChartObject
    Dim intCount As Integer
    Dim i As Integer
    Dim j As Integer
    Dim n As Integer
    Dim sngPos() As Single
    Dim intIndex() As Integer

    ' Initialize
    intCount = ActiveSheet.ChartObjects.Count
    ReDim sngPos(1 To intCount)
    ReDim intIndex(1 To intCount)
    For i = 1 To intCount
    sngPos(i) = ActiveSheet.ChartObjects(i).Top
    ' Temp name that doesn't overlap with final names
    ActiveSheet.ChartObjects(i).Name = "Cht" & i
    intIndex(i) = i
    Next i

    ' Bubble sort
    For i = 1 To intCount - 1
    For j = i + 1 To intCount
    If sngPos(j) < sngPos(i) Then
    n = intIndex(i)
    intIndex(i) = intIndex(j)
    intIndex(j) = n
    End If
    Next j
    Next i

    ' Rename - modify starting number as desired
    For i = 1 To intCount
    ActiveSheet.ChartObjects(intIndex(i)).Name = "Cht" & (i + 1000)
    Next i
    End Sub

    The macro will initially rename the charts Cht1, Cht2, etc. in the order they are now. Then, after determining the top-down order, they will be renamed Cht1001, Cht1002, etc. You can modify this as desired.

Posting Permissions

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