Thread: Excel 2007 Chart VBA tips

Hi,
More often than not the various solutions posted for manipulating charts in Excel 2007 with VBA are issue specific, which may be adapted by other users to suit their needs.
This is great, but often means trolling a large number of threads and posts before you find something you can use/adapt.

Given that chart methods are very difficult to track down and the VBA chart object model in XL07 isn't terribly helpful, and to possibly help users with basic manipulation I've started this thread in the hope that others will contribute snippets of code for making changes to charts in XL07 using VBA.

To start the code below can be used to make the chart border invisible for every chart in the active sheet.

Code:

Sub Remove_Chart_Border()
' Use this to make the border on each chart in the active sheet invisible
'
' Get the number of charts in the active sheet
NumberOfChartsInActiveSheet = ActiveSheet.ChartObjects.Count
'
'Create a For/Next loop to cycle through each chart in the active sheet
For ChartLoop = 1 To NumberOfChartsInActiveSheet
'
'activate the next chart in the loop
ActiveSheet.ChartObjects(ChartLoop).Activate
' Do something with the active chart
With ActiveChart
' In this case the "do something" is set the line of the border of the Chart Area to invisible (visible = false)
    .ChartArea.Format.Line.Visible = msoFalse
End With
'
'move on to next chart in the loop
Next
'
'
End Sub

My knowledge of manipulating charts with VBA is very limited, so I'm hoping other (likely more experienced) forum members will see this as a good idea and tag on.

Change Position of Legend

The below can be used to activate each chart in the activesheet and reposition the legend based on values entered by users.

Code:

Sub Change_Position_Of_Legend()
'
' For each chart in the active sheet, activate chart and change position of Legend
'
Dim NumberOfChartsInActiveSheet As Integer ' create a number variable to reflect the number of charts in the active sheet
Dim LegendDistanceFromTop As Double ' create a number variable to reflect the desired distance of the Legend from the TOP of the active chart
Dim LegendDistanceFromleft As Double ' create a number variable to reflect the desired distance of the Legend from the LEFT of the active chart
'
' Get the number of charts in the active sheet
NumberOfChartsInActiveSheet = ActiveSheet.ChartObjects.Count
'
' Ask the user to specify value for the "LegendDistanceFromTop" number
LegendDistanceFromTop = CDbl(InputBox(Prompt:="Enter the 'Distance From Top' for the chart Legends", Title:="Chart Legend Attributes"))
' use the below to use a cell value to specify the distance from Top
' LegendDistanceFromTop = CDbl(range("B2").value)' where cell B2 contains a whole number
'
' Ask the user to specify value for the "LegendDistanceFromleft" number
LegendDistanceFromleft = CDbl(InputBox(Prompt:="Enter the 'Distance From Left' for the chart Legends", Title:="Chart Legend Attributes"))
' use the below to use a cell value to specify the distance from Left
' LegendDistanceFromLeft = CDbl(range("B3").value)' where cell B3 contains a whole number
'
' Turn off screen updating to help the code run faster
Application.ScreenUpdating = False
'
' Create a For/Next loop to cycle through each chart in the active sheet
For ChartLoop = 1 To NumberOfChartsInActiveSheet
'
' Activate the next chart in the loop
ActiveSheet.ChartObjects(ChartLoop).Activate
' Now do something with the active chart
With ActiveChart
' In this case the "do something" is move the chart legend by changing the distance from the TOP and LEFT of the active chart
    .Legend.Top = LegendDistanceFromTop
    .Legend.Left = LegendDistanceFromleft

End With
'
'move on to next chart in the loop
Next
'
'
Application.ScreenUpdating = True
'
' Simple message to let users know that the repositioning is finished
MsgBox ("The Legend on each chart in the active sheet has been repositioned")
End Sub