Automate Excel

VBA: Size an Object to a Range Size

You can size an object like Pictures, Autoshapes, and Charts to be the same size as a Range. To do this, set the objects .Left .Top .Width and .Height properties equal to the respective properties of a Range.

The following example sizes a Chart to the Range B2:D6

The VBA code used to accomplish this:

Sub SizeChart2Range()

Dim MyChart As Chart
Dim MyRange As Range

Set MyChart = ActiveSheet.ChartObjects(1).Chart
Set MyRange = Sheet1.Range("B2:D6")

    With MyChart.Parent
        .Left = MyRange.Left
        .Top = MyRange.Top
        .Width = MyRange.Width
        .Height = MyRange.Height
    End With

End Sub

Posted in VBA |

3 Responses

1. Pedro Says:
   February 12th, 2005 at 3:50 pm

   Hi,

   I tried to modify the code for a shape but I could not.
   Would you tell me what I am doing wrong.
   Thanks in advance for your kind assistance.
   Pedro.

   Sub SizeShape2Range()

   Dim MyShape As Shape
   Dim MyRange As Range

   Set MyShape = ActiveSheet.Shapes(1).Shape ‘I THINK HERE IS MY ERROR
   Set MyRange = Sheet1. Range(”B2:D6″)

   With MyShape.Parent
   .Left = MyRange.Left
   .Top = MyRange.Top
   .Width = MyRange.Width
   .Height = MyRange.Height
   End With

   End Sub

2. Mark Says:
   February 12th, 2005 at 4:14 pm

   Hey pedro, try this, it works for me to use this concept with an autoshape

   Sub SizeShape2Range()

   Dim MyShape As Shape
   Dim MyRange As Range

   Sheet1.Activate

   Set MyShape = ActiveSheet.Shapes(”AutoShape 1″)
   Set MyRange = Sheet1.Range(”B2:D6″)

   With MyShape
   .Left = MyRange.Left
   .Top = MyRange.Top
   .Width = MyRange.Width
   .Height = MyRange.Height
   End With

   End Sub

3. Pedro Says:
   February 13th, 2005 at 8:00 am

   Yes, Mark!!!!.

   It works perfectly. Thanks so much for your help.

   regards,
   Pedro

Leave a Comment