Position and Resize Rectangles Dynamically, Based on Cell Values

[Mattman55]

I've been working on this all day and have had zero success. I want to be able to change dates in Columns A and B and have rectangles position and lengthen themselves accordingly, based on the calculated values in Columns C and D. I almost had something working earlier today but it would only change the bar length if I double-clicked inside Column D and then hit enter (i.e. it wasn't truly dynamic). I don't have any meaningful code to share because I've been chopping it all up throughout the day in various bouts of frustration.

I don't want to use conditional formatting, because the user will have the option to switch the timescale to monthly, quarterly, yearly, etc., so I'll have to handle the length calculations (Column D) separately based on that criteria. If someone can give me a seed to start with, I think I can water it until it grows into my final vision.

Thanks in advance for your time. I was trying not to bother y'all but I'm in over my head.

 

[vw412]

Are you using Shapes for the bars? It might also help if you told us more about what you have already tried. What was you original design? Just off the top, you will need to find event that will trigger the bar modifications for them to be dynamic. A cell with a formula will not generate the Change event so you can't use that. Is there any other operation you would do with this sheet manually that might be the trigger, e.g. user enters date in A or B?

 

[Mattman55]

Yes, I'm using shapes. I originally started with this code that I found on the web and tried to modify it for rectangles. The line "If Not Intersect(Target, Range("A1:B1")) Is Nothing Then" was (I thought) supposed to look for a trigger "event" so the shape would change. I could not get it to work. And, yes, changing dates in Columns A or B would ideal be a triggering events for the script to run. My shapes are named Rectangle 1 and Rectangle 2.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Count = 1 Then
If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
Call SizeCircle("Oval 2", Array(Val(Range("A1").Value), Val(Range("B1").Value)))
End If
End If
End Sub
Sub SizeCircle(Name As String, Arr As Variant)
Dim I As Long
Dim xCenterX As Single
Dim xCenterY As Single
Dim xCircle As Shape
On Error GoTo ExitSub
For I = 0 To UBound(Arr)
If Arr(I) > 10 Then
Arr(I) = 10
ElseIf Arr(I) < 1 Then
Arr(I) = 1
End If
Next
Set xCircle = ActiveSheet.Shapes(Name)
With xCircle
xCenterX = .Left + (.Width / 2)
xCenterY = .Top + (.Height / 2)
.Width = Application.CentimetersToPoints(Arr(0))
.Height = Application.CentimetersToPoints(Arr(1))
.Left = xCenterX - (.Width / 2)
.Top = xCenterY - (.Height / 2)
End With
ExitSub:
End Sub

Then I found this code to look for a trigger event, but again could not get it to work. Instead of the MsgBox, I tried to insert a command to call my subroutine.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "This Code Runs When Cell A1 Changes!"
End If
End Sub

Are you using Shapes for the bars? It might also help if you told us more about what you have already tried. What was you original design? Just off the top, you will need to find event that will trigger the bar modifications for them to be dynamic. A cell with a formula will not generate the Change event so you can't use that. Is there any other operation you would do with this sheet manually that might be the trigger, e.g. user enters date in A or B?

 

[vw412]

Yeah, any time you depend on the Worksheet_Change event or the Workbook_SheetChange event to trigger some action, you must remember they only fire when an action outside the sheet happens to change the cell. A formula in the cell will not trigger these events.

Next question, how do you determine the placement of the bars? You mentioned they could be altered to represent different time periods. Are they related to the columns in the sheet for positioning?

I am experimenting with an idea for this but it needs some work before showing.

 

[Mattman55]

Yeah, any time you depend on the Worksheet_Change event or the Workbook_SheetChange event to trigger some action, you must remember they only fire when an action outside the sheet happens to change the cell. A formula in the cell will not trigger these events.

Next question, how do you determine the placement of the bars? You mentioned they could be altered to represent different time periods. Are they related to the columns in the sheet for positioning?

I am experimenting with an idea for this but it needs some work before showing.

Thank you for working on this! The bar positions can't really be related to columns because the timescale will sometimes be in fiscal quarters, or years, so a bar that starts in May, might have to start in the middle of a Q2 column, for example. That's another reason I can't use conditional formatting. I'm playing with formulas to adjust the starting location based on start dates and whether the timescale is in months, quarters, years, etc. Suffice it to say I think I can manage that part, as long as I have a solution that looks to a certain cell for a "starting point" value. I'll work on coming up with the correct positional values on my own, once I have the syntax dialed in. My thought is that relative horizontal positioning is on some linear scale, like pixel counts. Once I can convert days to that scale, and adjust the results based on whether the user-selected timescale is monthly, quarterly, bi-annually, etc., the positioning should fall into place. I will probably have a "fudge factor" column where the user can tweak the bar position manually by adding a supplemental ± number as might be needed.

I will also have "milestone" shapes (diamonds) that fall on top of the bars, based on dates provided by the user. I didn't want to complicate my request by mentioning that, but I think once I have the basic vba script I should be able to adapt it for "operation diamond." Thanks again.

 

[vw412]

I will work with the assumption that E1 will be the date of the first day of a period whether it is month quarter, or year. Have you determined how the periods will be determined? I will be working on my ideas and probably bounce some of them off you. This seems to be an exciting challenge.

 

[Mattman55]

I will work with the assumption that E1 will be the date of the first day of a period whether it is month quarter, or year. Have you determined how the periods will be determined? I will be working on my ideas and probably bounce some of them off you. This seems to be an exciting challenge.

True, you can assume that E1 will be the date of the first day of a period. As this tool is used from project to project, some projects are short duration (months) and some span half a decade. I let the user select (M)onth, (Q)uarter, (B)iannual, or (Y)ear. From there, the timescale will reconfigure itself accordingly. I have a preliminary working version of that already set up (see screenshot).

If you have anything, even rough, to send me I'll be very grateful. I'm on a deadline to get something working this week. Thanks again!

 

[vw412]

How can I send you a workbook for you to check over? I have coded up a Class that seems to be working as yuo want so far. Still needs some work but I want you to see what I have so far.

 

[Mattman55]

How can I send you a workbook for you to check over? I have coded up a Class that seems to be working as yuo want so far. Still needs some work but I want you to see what I have so far.

<<email address removed>> Thank you so much!

 

[vw412]

will send it right away.