Excel Moving Graphs

[shivam03]

Hi there,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I hope you are all well.<o></o>
<o></o>
I have created a dashboard in Excel which contains a few graphs. The worksheet is protected and the objects are locked.<o></o>
<o></o>
The problem is that when I return to the worksheet the position of the graphs in the worksheet often changes. <o></o>
<o></o>
Nothings changes in the worksheet apart from the data in the tables.<o></o>
<o></o>
Can anyone suggest why this could be happening?<o></o>
<o></o>
Kind regards,<o></o>

Shivam03<o></o>

 

[JoeMo]

I can't tell you why it happens, but I've seen it happen too. One workaround is to have a macro for the workbook_open event that positions the graphs where you want them to be, usually with reference to a specific range on the sheet they're embedded in.

 

[shivam03]

Thanks.

Do you have an example of the marco I could use?

Kind regards,

Shivam

 

[JoeMo]

Thanks.

Do you have an example of the marco I could use?

Kind regards,

Shivam

Put the module below in a regular module. Then in a 'Thisworkbook' module for the Workbook_Open event simply call the module below:

Sub PutChartOnRange(rng As Range, chtName As String)

'This routine places an embedded chart over a specific range on the worksheet
'The chart is automatically sized to cover the user-defined range called rng

ActiveSheet.ChartObjects(chtName).Activate
    With ActiveChart.Parent
        .Left = rng.Left
        .Width = rng.Width
        .Top = rng.Top
        .Height = rng.Height
    End With

End Sub

 

[shivam03]

Thanks for this, it's great.

Do you know how to work out the current location of my charts?

Thanks,

Shivam

 

[JoeMo]

Thanks for this, it's great.

Do you know how to work out the current location of my charts?

Thanks,

Shivam

This will give you the position of an embedded chart (here Chart 1, change chart name to fit) in points.

Sub GetChartPosition()
Dim cT, cL, W, H
ActiveSheet.ChartObjects("Chart 1").Activate 'substitute your chart name
With ActiveChart.Parent
    cT = .Top
    cL = .Left
    W = .Width
    H = .Height
    
    MsgBox cT & vbNewLine & cL & vbNewLine & W & vbNewLine & H
End With
End Sub

 

[shivam03]

Thank you very much.