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
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