how to make fixed chart size

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I am trying to make a code that will cause a chart to remain its present size despite changes in columns or rows. Got this from recording a macro but want to change code to a generic format so I can run it on any chart and not just 'Chart19'

Even better would be code that would cycle through all charts on a tab and apply this macro to them as well.

Thanks in advance for any assistance.

Code:
Sub ChartFixSize()
'
' ChartFixSize Macro
' Select chart and then run this macro to make it so that it does not resize when rows or
' columns are changed. 10/10/2006 by Jon
'

'
    With Sheets("Sheet1").DrawingObjects("Chart 19")
        .Placement = xlMove
        .PrintObject = True
    End With
    Sheets("Sheet1").DrawingObjects("Chart 19").Locked = True
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
You can iterate through all of the DrawingObjects on a worksheet:
Code:
Sub Lock_Objects()

For Each obj In Sheets("Sheet2").DrawingObjects
    obj.Placement = xlMove
    obj.Locked = True
Next obj

End Sub

-Tim
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
Is there a way to adjust the code to use the active sheet rather than a named sheet?
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
Sure.

Code:
Sub Lock_Objects()

For Each obj In ActiveSheet.DrawingObjects
    obj.Placement = xlMove
    obj.Locked = True
Next obj

End Sub
 

Forum statistics

Threads
1,136,515
Messages
5,676,302
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top