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

krllb

New Member
Joined
Jun 10, 2013
Messages
16
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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:
Upvote 0
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:
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top