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
Leave a comment | Trackback
  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