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

krllb

New Member
Joined
Jun 10, 2013
Messages
11
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
Joined
Mar 11, 2015
Messages
4,624
Office Version
365
Platform
Windows
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
Joined
Jun 10, 2013
Messages
11
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
Joined
Mar 11, 2015
Messages
4,624
Office Version
365
Platform
Windows
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
Joined
Mar 11, 2015
Messages
4,624
Office Version
365
Platform
Windows
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:

Forum statistics

Threads
1,081,530
Messages
5,359,349
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top