VBA Make Existing Shape Extend to Certain Cell

[tonpob]

Hi there I'm very new to vba and I was wondering if it were possible to lengthen an existing shape for example rectangle from C2:E2, and have it go from C2:G2 by clicking a command button?

I'm attempting to create a 1 year look ahead with arrows stretching from the first month on the schedule (ie. January) to the month that activity ends. I've been able to get by so far by creating a macro that changes shape width according to where the month you want to end at is.

I run into issues though when the activity doesn't start at the first month on the schedule. When I run the macro on a shape (activity) that begins in May and ends in October the shape will take the width of a shape that runs from January to October and extend way past October.

Thanks for reading and any help would be appreciated!

 

[AlphaFrog]

Try something like this...

    [COLOR=darkblue]With[/COLOR] ActiveSheet.Shapes("Rectangle 1")
        .Top = Range("C2:G2").Top
        .Left = Range("C2:G2").Left
        .Width = Range("C2:G2").Width
        .Height = Range("C2:G2").Height
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[tonpob]

That's great thanks alpha! Is there anyway though to make it so that instead of "Rectangle 1" it would affect the shape that I selected? And also would it be able to start from where that selected shape starts and end at a certain cell?
Sorry if this isn't making much sense

 

[AlphaFrog]

That's great thanks alpha! Is there anyway though to make it so that instead of "Rectangle 1" it would affect the shape that I selected? And also would it be able to start from where that selected shape starts and end at a certain cell?
Sorry if this isn't making much sense

This will adjust the selected shape's width from its current location to column G2

    Selection.Width = Range(Selection.TopLeftCell, Range("G2")).Width

Two caveats:
*The shape starts before the end column (column G in this case)
*The shape starts exactly at the edge of a column.

 

[tonpob]

This will adjust the selected shape's width from its current location to column G2

    Selection.Width = Range(Selection.TopLeftCell, Range("G2")).Width

Two caveats:
*The shape starts before the end column (column G in this case)
*The shape starts exactly at the edge of a column.

I think I can work with this, thanks again!