Re-sizing a Shape, WIDTH IS ALWAYS A PROBLEM!

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
I have created charts in excel with VbA and copid them into another sheet with paste special. I am trying to resize them.
I learned that I needed to resize them as a shape so I am using the code below to resize: I am sending in the sheet with the two charts copied to start in range a35 and j35 respectively, which they do.

Sub LineUpMyCharts(InSheet As Worksheet)
Dim MyWidth As Single, MyHeight As Single
Dim NumWide As Long
Dim iChtIx As Long, iChtCt As Long

MyWidth = 200
MyHeight = 300
NumWide = 3

iChtCt = InSheet.Shapes.Count
For iChtIx = 1 To iChtCt
With InSheet.Shapes(iChtIx)
.LockAspectRatio = msoFalse
.Width = MyWidth
.Height = MyHeight
.Top = Int((iChtIx - 1) / NumWide) * MyHeight + 475
End With
Next
End Sub

Everything works except for the width portion. Any ideas why? Or another way to format???? Thanks
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
Additionally, when I get the error message I hit the green run button again and it will correctly format it but I obviously still need to get the error message resolved. I don't understand why it would run after hitting the run button again, despite the error message.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,542
Members
425,480
Latest member
br400821

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