VBA: Size an Object to a Range Size

December 5th, 2004 | Categories: VBA | Tags:
-->

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

sizechart2range

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
Can't get the tutorial to work for you? Need help with your code?
Get answers right away at our AE Excel Support Forums!
  1. Pedro
    February 12th, 2005 at 15:50
    Reply | Quote | #1

    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
    February 12th, 2005 at 16:14
    Reply | Quote | #2

    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
    February 13th, 2005 at 08:00
    Reply | Quote | #3

    Yes, Mark!!!!.

    It works perfectly. Thanks so much for your help.

    regards,
    Pedro