Chart Object VBA

[RECrerar]

Hi,

I have a chart in the group footer of one of my reports for which the data and teh series names changes for each set of data. Having a standard set up for this chart can end up looking messy, especially the size and placing of the legend.

I'm therefore thinking of writing some code that will change the size and position of the legend and the data area to neatly fit the data.

By only problem is that I can't find any information on how to control the chart object in VBA. I was wondering if anyone could provide information or a link to a site that will tell me how to refer to different parts of the chart through VBA, specifically the positioning of chart items

Thanks
Robyn

 

[RECrerar]

Hi again,

I have been having more of a look at this and am getting very stuck so thought I would give more detail of the specific task I would like to perform and see if anyone can help.

The legends in my charts have very different sizes due to the different series names from different data sets. Therefore what I want the code to do is, look at the height and width of the legend and from this work out where to place it. It should be on the right hand side of the chart (with the plot area sized to be as large as it can be in the space not taken up by the legend) and centered height wise.

The code I have been trying to use is shown below. However this keeps the legend in the bottom left of the chart and overlapping the plot area.

Const twips As Long = 567
 
With Me.Graph1
    .SizeMode = 0
    .Left = 0 * twips
    .Top = 0 * twips
    .Width = 18 * twips
    .Height = 9 * twips
    .haslegend = True
    .HasTitle = False
End With
 
Me.Graph1.Activate
Dim LHeight, LWidth As Double
 
LHeight = Me.Graph1.legend.Height
LWidth = Me.Graph1.legend.Width
 
With Me.Graph1
    .haslegend = True
    .legend.Left = (18 - (LWidth / 10)) * twips 
    .legend.Top = (4.5 - (LHeight / 20)) * twips 
    .legend.Width = (LWidth / 10) * twips
    .legend.Height = (LHeight / 10) * twips
    .plotarea.Left = 0
    .plotarea.Width = (10 - (LWidth / 10)) * twips
    .legend.Font.Size = 10
    .legend.Font.Name = "Arial"
End With

Exit Sub
ChartObject_Err:
MsgBox Err.Description, , "ChartObject()"
Resume ChartObject_Exit

This is in the onformat even of the report footer where the chart is. The deviding by 10 or 20 is because I think it returns the legend width in mm. I am completely stuck so if anyone could help it would be greatly appreciated.

Regards
Robyn

 

[The Smiley One]

Instead of having .legend.left and .legend.top have you tried setting
.legend.position=xlright