VBA to change chart size

[chipgiii]

I do numerous charts and would like a macro that when I select a chart it resizes it based on pre-determined code. Many of my charts go into Powerpoint and I generally use one of three sizes: kind of a small, medium and large thing. I attempted to record a macro for three different sizes, but when I want to select a new chart, it returns to the original one selected. So for instance I want one that is 2.3 X 4 (aspect ratio has to be false). A second size is 3.5 X 5. Once I create the first one, I can not use it on any other chart.

My VBA skills are nearly non-existent, so bare with me - please!

Hope someone can help
Chip

 

[CDICKENS]

What is the 2.3 X 4, is that inches?

Chuck

 

[CDICKENS]

Try these two macros.

You can play with the height and width values to get the desired size.

Also, make sure the desired chart is selected before running the macro.

Sub ChartSize24()
'
' '
    With ActiveChart.Parent
    .Height = 180 ' resize 2.5 pt at 72 ppi.
    .Width = 288 ' resize 4.0 pt at 72 ppi.
  
    End With


End Sub
Sub ChartSize35()
'
'    With ActiveChart.Parent
    .Height = 252 ' resize 3.5 pt at 72 ppi.
    .Width = 360 ' resize 5.0 pt at 72 ppi.
  
    End With


End Sub

Chuck

 

[chipgiii]

It runs perfect as long as I run it from the developer's tab. As soon as I create and assign a button I get debug error:

Sub ChartSize24()
'
' '
With ActiveChart.Parent <--this is highlighted in yellow.
.Height = 180 ' resize 2.5 pt at 72 ppi.
.Width = 288 ' resize 4.0 pt at 72 ppi.

End With

End Sub

 

[CDICKENS]

Did you select the chart first?

 

[chipgiii]

Yes, I do. The charts I am using are dynamically updated each month. They are then copied into one of three reports. Depending on the text that may accompany as a result of the data, will often determine the chart size. So there have been months when the same chart is in three different reports, all at different sizes. I am hoping that I can select the chart, hit a button, and it gives me size, and I can change the code to create several sizes that are used often. Hope that makes sense?

 

[CDICKENS]

what is the exact debug error?

 

[chipgiii]

Chuck,

When do you sleep?

The macro runs perfectly if I select the chart, and then go to the ribbon menu, click on "Macros" and select the macro and click run. If I select the chart and try to click a button I get the error. "Run-time error '91': Object variable or With block variable not set

Once you select the chart the curso changes to the cross with arrows (I call it the grab cursor), and wants to stay that way when you select the button. Hope that makes sense.

I could put the file on skydrive if that will help.

 

[CDICKENS]

Uploading to skydrive would be a big help.

The action of clicking the button is removing focus from the chart.

Maybe try creating a userform at the beginning of the macro that allows you to select the correct size option, then have the macro use that answer to size the chart appropriately.

Chuck

 

[chipgiii]

Chuck,

I will definitely try that. Thanks for all your help. Bottom line is I can always use what you've provided regardless.