simple change chart in VBA

LKnomad · #1 07-05-2012, 10:13 AM

I am trying to make a simple change to a single selected chart in a word document using VBA. I cannot seem to make this work. Any help appreciated.

The code that I have been trying looks like this:

Code:

 
Sub ChangePieChart()
 
Dim objshape as chart
  
 objshape.Height = 250 
     
End Sub

This should be so simple but I cannot figure it out. I get a variety of errors when I try different options.

macropod · #2 07-05-2012, 01:28 PM

Hi LKnomad,

You haven't told your macro what objshape is. Try:

Code:

Sub ChangePieChart()
Dim objShape As Shape
Set objShape = Selection.ShapeRange(1)
objShape.Height = 250
End Sub

This could be reduced to:

Code:

Sub ChangePieChart()
Selection.ShapeRange(1).Height = 250
End Sub

LKnomad · #3 07-05-2012, 01:38 PM

Thanks for helping out. When I run your code I get the following error

"Invalid procedure call or agrument"

The error points to
Set objShape = Selection.ShapeRange(1)

I get an error using either version of your code. VBA is going to kill me...

macropod · #4 07-05-2012, 01:46 PM

That tells me you didn't have a shape object selected when you ran the code. Since you originally said you're:

Quote:

trying to make a simple change to a single selected chart in a word document using VBA

I didn't add any error-checking.

LKnomad · #5 07-05-2012, 01:51 PM

I had the chart selected when I ran the code. Is there an alternate code - or some kind of error checking I can add in.

macropod · #6 07-05-2012, 01:54 PM

In that case, your chart may be formatted as an inline shape. Try changing:
Dim objShape As Shape
to:
Dim objShape As InlineShape

LKnomad · #7 07-05-2012, 02:08 PM

This is my code at this time - Same error

Sub individualPieChart()

Dim objShape As InlineShape

Set objShape = Selection.ShapeRange(1)
objShape.Height = 250

end sub

macropod · #8 07-05-2012, 02:13 PM

Try:

Code:

Sub ChangePieChart()
With Selection
  If .ShapeRange.Count > 0 Then
    .ShapeRange(1).Height = 250
  ElseIf .InlineShapes.Count > 0 Then
    .InlineShapes(1).Height = 250
  End If
End With
End Sub

LKnomad · #9 07-05-2012, 02:18 PM

That did it - thanks!

But why is it so complecated?

macropod · #10 07-05-2012, 02:21 PM

It's not complicated, just flexible and error-proof. The code now works with both in-line and floating shapes and won't generate an error if none is selected.

LKnomad · #11 07-05-2012, 02:22 PM

Nevermind I simplified it. Thanks Paul, you're the best!

Sub ChangePieCharttest()
With Selection

.InlineShapes(1).Height = 200
.InlineShapes(1).Width = 465

End With
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
It has to be simple...formula	ClearSkies	Excel	1	03-04-2011 01:06 PM
Simple or what?	nebb	Publisher	0	09-23-2010 07:00 AM
Converting manual chart to bar chart?	aligahk06	Excel	0	07-03-2010 12:23 PM
Simple formulas	den hobbelder	Word	1	02-26-2010 01:53 PM
Chart colors change during slideshow	chickasaw	PowerPoint	0	02-06-2010 06:08 PM