Results 1 to 1 of 1
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Thumbs up Unanswered: ReName all Charts in a WorkBook

    He some code

    A workbook I was given Gots Over 150 charts I need to update the Boards for Each Chart

    after Many Right Click

    Got me thinking

    Code:
    Sub Build_Chart_List()
    Dim Thischart As ChartObject
    Dim ThisSheet As Worksheet
    Dim MyRows As Long
    Application.Calculation = xlManual
    MyRows = 0
    For Each ThisSheet In Worksheets
        Debug.Print ThisSheet.Name
        'ActiveWorkbook.Sheets(ThisSheet.Name).Activate
        For Each Thischart In Sheets(ThisSheet.Name).ChartObjects
            Thischart.Name = Thischart.Chart.ChartTitle.Text 'make the object the same name as the TITLE of the Chart I hate seeing Chart 1 .. Chart 2..
            Range("Chart_Names").Offset(MyRows, 0).Value = Thischart.Name
            Range("Chart_Names").Offset(MyRows, 1).Value = ThisSheet.Name
            MyRows = MyRows + 1
        Next
    Next
    Application.Calculation = xlAutomatic
    End Sub
    Know Update the Charts Borders base on a cell

    Code:
    Sub Set_Border_Colour()
    Dim MyRows As Long
    Do Until Range("Chart_Names").Offset(MyRows, 0) = ""
            This_Chart = Range("Chart_Names").Offset(MyRows, 0).Value
            This_Sheet = Range("Chart_Names").Offset(MyRows, 1).Value
            ActiveWorkbook.Sheets(This_Sheet).Activate
            ActiveSheet.ChartObjects(This_Chart).Chart.ChartArea.Border.Color = Range("Chart_Names").Offset(MyRows, 2).Value
            MyRows = MyRows + 1
    Loop
    End Sub
    How the Delete the list just case hes added some NEW ones LOL


    Code:
    Sub Clear_Chart_List()
    Range("Chart_names").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.Offset(0, 1)).Select
    Selection.Clear
    Range("Chart_names").Select
    Call Build_Chart_List
    End Sub
    if the User add more charts I can rebuild the List and

    run the Set_Boarder_Colour Sub Job Done
    Last edited by myle; 05-22-15 at 00:54. Reason: spelling
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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