VBA Make Existing Shape Extend to Certain Cell

tonpob

New Member
Joined
Feb 5, 2015
Messages
6
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
Try something like this...

Code:
    [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

New Member
Joined
Feb 5, 2015
Messages
6
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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

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

New Member
Joined
Feb 5, 2015
Messages
6
This will adjust the selected shape's width from its current location to column G2

Code:
    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!
 

Forum statistics

Threads
1,081,841
Messages
5,361,638
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top