Change shape size (Rectangle) based on two cell values (Start Date) & (End Date) Excel 2013

krllb

New Member
Hi Excel Guru's,


Im trying to create a scheduler where in a shape (rectangle) will be based on two cells (Start Date) and (End Date).

Both shapes and date values are on the same sheet.

Can you help me? Advance thanks to you all.
 

Yongle

Well-known Member
Which cell is Start Date ?
Which cell is End Date ?
Are they named ranges?
Are they always the same cells?


:confused: - how many shapes are there ?
- your post is not consistent

a shape (rectangle) will be based on two cells
Both shapes

One way ..
Code:
Sub Size()
    Dim shp As Shape, cel As Range
    
    With ActiveSheet
        Set shp = .Shapes("Rectangle 1")
        Set cel = .Range("B2")
    End With
    With shp
        .Height = cel.Height
        .Width = cel.Width
    End With

End Sub
 
Last edited:

krllb

New Member
Hi Yongle,

Thank you for your response.

For now I am starting with just 1 rectangular shape.

Which cell is Start Date ? B2
Which cell is End Date ? C2
Are they named ranges? No
Are they always the same cells? Yes.

I am trying to create a scheduler where in I can display the schedule of an item (ex. Program Development Phase) based from its start date and end date.

_________A___________________B_______C_____D____E_____F______G____H_____I____J______K_____L
1 Program Development Phase Start Date End Date Oct 23
Oct 24 Oct 25 Oct 26 Oct 27 Oct 28 Oct 29 Oct 30 Oct 31
2 ________________________Oct 23____Oct 29 --------------------------------------------------->
3
4

I want it to be dynamic so when I change the date it would reflect on the Shape.
Apologies, im having a hard time aligning the columns.

Hope you could help. Thanks!


 
Last edited:

Yongle

Well-known Member
I am having a hard time understanding precisely what you want :confused:
What exactly do you mean by "reflect on the Shape" ?

your shape is named "rectangle 1"
B2 = Oct 23
C2 = Oct 29

Are you wanting "rectangle 1" to be resized so that it stretches from D2 to J2 ?
 

Yongle

Well-known Member
see post#4

1. run from sheet containing shape and values
2. The values in row 1 and those in B2 and C2 are DATES (not text)

Code:
Sub Resize()
    Dim L As Range, R As Range
    Set L = Rows(1).Find(Range("B2")).Offset(1)
    Set R = Rows(1).Find(Range("C2")).Offset(1)
    With ActiveSheet.Shapes("Rectangle 1")
        .Left = L.Left
        .Top = L.Top
        .Width = Range(L, R).Width
        .Height = L.Height
    End With
End Sub
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
1
StartEnd
Oct 23​
Oct 24​
Oct 25​
Oct 26​
Oct 27​
Oct 28​
Oct 29​
Oct 30​
Oct 31​
Nov 01​
Nov 02​
2
Oct 23​
Oct 29​
Sheet: Sheet1
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top