Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi all,

    i have large number of charts with legend at the top of the chart area, and my question is there a way to center the legend for all the charts in one shot, rather than doing them one by one.

    dubdub
    TIA
    dubdub

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I think a VBA routine is the way to go.
    Are all these charts in one file, or is it a series of files?
    If more than one file are they in the same directory?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Catharine,

    they are in one xls file, and if possible can the legend box dimensions be managed as well.
    TIA
    dubdub

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Sorry to take so long to get back to you. What version of Excel are you working with?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Catharine Richardson - WebGenii View Post
    Sorry to take so long to get back to you. What version of Excel are you working with?

    sorry too for late reply, 2003.

    dubdub
    TIA
    dubdub

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    The non-vba method would be to make a chart template and then apply to each in turn.

    In VBA one would cycle through all the charts in the file, centering each in turn.
    Here is a nice little snippet of code to start with. Unfortunately today, I am at a Office 2007 machine.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Catharine Richardson - WebGenii View Post
    The non-vba method would be to make a chart template and then apply to each in turn.

    In VBA one would cycle through all the charts in the file, centering each in turn.
    Here is a nice little snippet of code to start with. Unfortunately today, I am at a Office 2007 machine.
    Hi Catharine,

    i am no VBA expert, i used the reference code, it cycle through the chart one by one with one action, chart delete.

    dubdub
    TIA
    dubdub

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If you look at the Code Snippet you will see that it is called Chart Killer and the line says do you want to delete.
    That is why it deletes the charts
    You need to change the code so that it Centres the Chart Titles, BUT if you are NOT a VBA writer, then this will prove a bit of a trial

    The Code below does this

    Code:
    Sub CentreChartLegendsAtTop()
    
    Dim lngCharts As Long, lngChart As Long
    Dim wksSheet As Worksheet
    Dim intR As Integer
    
    intR = MsgBox("This will place each Chart Legend on Each Chart at the TOP" _
         & vbLf & "Note it does NOT change the Title Location IF there is one" _
         & vbLf & vbLf & "Do you want to proceed?", _
           vbYesNo + vbQuestion + vbDefaultButton1, "Centre Legends")
           
    If intR = vbYes Then
      'Loop Through the Sheets in the Workbook
      For Each wksSheet In ThisWorkbook.Sheets
        'Count if ANY Cahrt Objects
        lngCharts = wksSheet.ChartObjects.Count
        'If there are then process each one at a time
        If lngCharts > 0 Then
            'Activate the Sheet
            wksSheet.Activate
            'No loop through each of the Chart Objects
            For lngChart = lngCharts To 1 Step -1
                 'Activate the Chart and then set the Legend Position to the Top
                 wksSheet.ChartObjects(lngChart).Activate
                 ActiveChart.Legend.Position = xlTop
            Next  'Get Next Chart
         End If
      Next   'Get Next Sheet
    End If
    
    MsgBox "All Chart Legend Centred at the Top", vbInformation
    
    End Sub
    Andrew

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    many thanks Andrew,

    does it needs major changes to limit it to active sheet and/or a specific sheet.

    dubdub
    TIA
    dubdub

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Nope See Below
    But this NOW only does a single specified sheet

    Code:
    Sub CentreChartLegendsAtTopSpecificSheet()
    
    Dim lngCharts As Long, lngChart As Long
    Dim wksSheet As Worksheet
    Dim intR As Integer, strSheetName As String
    
    intR = MsgBox("This will place each Chart Legend on Each Chart at the TOP" _
         & vbLf & "Note it does NOT change the Title Location IF there is one" _
         & vbLf & vbLf & "Do you want to proceed?", _
           vbYesNo + vbQuestion + vbDefaultButton1, "Centre Legends")
           
    If intR = vbYes Then
        strSheetName = InputBox("Enter Name Of Sheet or Use Active for Current Sheet", "Get Sheet Name", "ACTIVE") & ""
        If UCase(strSheetName) = "ACTIVE" Then
            Set wksSheet = ActiveSheet
            strSheetName = ActiveSheet.Name
        Else
            On Error Resume Next
            Set wksSheet = ActiveWorkbook.Worksheets(strSheetName)
            If Err.Number <> 0 Then
               MsgBox "That Sheet name was Invalid"
               Exit Sub
            End If
            On Error GoTo 0
            wksSheet.Select
        End If
        'Count if ANY Chart Objects
        lngCharts = wksSheet.ChartObjects.Count
        'If there are then process each one at a time
        If lngCharts > 0 Then
            'No loop through each of the Chart Objects
            For lngChart = lngCharts To 1 Step -1
                 'Activate the Chart and then set the Legend Position to the Top
                 wksSheet.ChartObjects(lngChart).Activate
                 ActiveChart.Legend.Position = xlTop
            Next  'Get Next Chart
         End If
    End If
    
    MsgBox "All Chart Legend Centred at the Top on " & strSheetName, vbInformation
    
    End Sub
    Andrew

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    many thanks Andrew, that's a great help.

    dubdub
    TIA
    dubdub

Posting Permissions

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