Move Triangle Shape based on cell value

[Bui Tao]

I hope someone can help me!

I want that when I enter a value in cell B3 (or B4), the triangle in cell F3 (or J4) moves along. Likewise, when I enter a value in cell C3 (or C4), the triangle in cell J3 (or Q4) will also move. And how to program when I have many TASK to do. It's too hard for me.
Many thanks.

 

[Micron]

Not nearly enough there to provide a definitive solution. F'rinstance,
whether or not the numbers in yellow cells correspond to day number;
where's 1 to 19 on the left
will there ever be 3 to 19 on the right
do numbers repeat because every month has (e.g. day 1)
So here's how to center a shape based on row 4 where you have 24/12/2022

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim shp As Shape
Dim i As Integer

If Target.Row = 4 And Target.Column = 10 Then
     i = Day(Cells(4, 10).Value)
     Set rng = ActiveSheet.Range(Cells(4, i - 14), Cells(4, i - 14))
     Set shp = Shapes("5-Point Star 2")
     shp.Left = rng.Left + (rng.Width - shp.Width) / 2
End If

End Sub

NOTE - my date was in J4 so that's why I used 4,10 for cell reference. It would be better to use the target address of the edited cell, but maybe this is a start. It all depends on the unknowns that I listed (and probably more). The Day function may not be relevant. I based it on the assumption that there's some sort of relationship between the numbers in yellow cells with the number of the day.
Not sure how much time I could spend on this given the impending Christmas holidays.

 

[Bui Tao]

Not nearly enough there to provide a definitive solution. F'rinstance,
whether or not the numbers in yellow cells correspond to day number;
where's 1 to 19 on the left
will there ever be 3 to 19 on the right
do numbers repeat because every month has (e.g. day 1)
So here's how to center a shape based on row 4 where you have 24/12/2022

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim shp As Shape
Dim i As Integer

If Target.Row = 4 And Target.Column = 10 Then
     i = Day(Cells(4, 10).Value)
     Set rng = ActiveSheet.Range(Cells(4, i - 14), Cells(4, i - 14))
     Set shp = Shapes("5-Point Star 2")
     shp.Left = rng.Left + (rng.Width - shp.Width) / 2
End If

End Sub

NOTE - my date was in J4 so that's why I used 4,10 for cell reference. It would be better to use the target address of the edited cell, but maybe this is a start. It all depends on the unknowns that I listed (and probably more). The Day function may not be relevant. I based it on the assumption that there's some sort of relationship between the numbers in yellow cells with the number of the day.
Not sure how much time I could spend on this given the impending Christmas holidays.

Oh, thank you so much. It's great. But I want to now that if I have 10 or 50 TASKS to do. There will be many triangles. How can you program?

 

[Micron]

Code would have to loop over something - either a set of shapes (and it gets the beginning and ending date values in the rows where the shape is located) or loop over the end and start dates and move the triangles accordingly. I think just about anything is doable, but that doesn't make the task easy. I forgot to mention that when I used Day function result to decide on where to put a shape, it was based on mm/dd/yyyy format, which is not what you are using. What Day returns with your date format would need to be determined IF it in fact, is even relevant. I posted that I wondered about that but so far, still no clarification.

I really think that without a copy of the file, this would take far too many posts to answer all the information that's missing at this point, and there would only be more to come.

 

[Bui Tao]

Code would have to loop over something - either a set of shapes (and it gets the beginning and ending date values in the rows where the shape is located) or loop over the end and start dates and move the triangles accordingly. I think just about anything is doable, but that doesn't make the task easy. I forgot to mention that when I used Day function result to decide on where to put a shape, it was based on mm/dd/yyyy format, which is not what you are using. What Day returns with your date format would need to be determined IF it in fact, is even relevant. I posted that I wondered about that but so far, still no clarification.

I really think that without a copy of the file, this would take far too many posts to answer all the information that's missing at this point, and there would only be more to come.

VBA test.xlsm

drive.google.com

Okey, you can access this link to get my file. Help me fix the code if I change the start day or finish day with year is 2023.
Thanks a lot!!

 

[Micron]

Count of information sought: 4 in post 2
Count of answers I received: 0

I guess you could just edit the If...End If part to work with your sheet, then copy and edit it 10 times for 10 tasks.

if I change the start day or finish day with year is 2023.

I have no idea what that means, or how the year makes any difference IF the number of the day is what matters.

 

[Anthony47]

A possible option, via formula, in case the problem is still pending:
-assuming that from F2 and subsequent cells contain the dates
-then set in F4 the formula

-format F4 with a font size of 20
-copy F4 to the right for as many dates you have in row 2
-copy this line of formulas downward for the expected list of activity

The result will be similar to the attached image, rows 4-6

You might use Wingdings 3 formatting, and obtain the result shown in row 8; in this case the formula in F8 is
[code]=IF(F$2=$B8,"s"&REPT(CHAR(173),3),IF(F$2=$C8,REPT(CHAR(173),3)&"s",IF(AND(F$2>$B8,F$2<$C8),REPT(CHAR(173),8),IF(AND(F$2<=TODAY(),F$2>$B8,$B8<>"",$C8=""),REPT(CHAR(173),7),""))))

(see the image for the real look of line 8)

byBUI TAO-MrEx_C21225_VBA test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Task	Start Day	Finish Day			2022-dic											2022-dic	2023-gen
2						20	21	22	23	24	25	26	27	28	29	30	31	01	02	03	04	05	06
3	Design Mold	20/12/2022	31/12/2022
4	Manufacture	21/12/2022	30/12/2022				V__	_____	_____	_____	_____	_____	_____	_____	_____	__V
5	Assemble1	24/12/2022								V__	____	____	____	____	____
6	Assemble2	22/12/2022	25/12/2022					V__	_____	_____	__V
7	Delivery
8	Task 1	26/12/2022	28/12/2022									s		s
9	Task 2
10	Task 3
11	Task 4
12	Task 5
Sheet1

Cell Formulas
Range	Formula
F1:W1	F1	=IF(MONTH(F2)<>MONTH(G2),TEXT(F2,"aaaa-mmm"),IF(MONTH(E2)<>MONTH(F2),TEXT(F2,"aaaa-mmm"),""))
G2:W2	G2	=F2+1
F4:W7	F4	=IF(F$2=$B4,"V__",IF(F$2=$C4,"__V",IF(AND(F$2>$B4,F$2<$C4),"_____",IF(AND(F$2<=TODAY(),F$2>$B4,$B4<>"",$C4=""),"____",""))))
F8:W8	F8	=IF(F$2=$B8,"s"&REPT(CHAR(173),3),IF(F$2=$C8,REPT(CHAR(173),3)&"s",IF(AND(F$2>$B8,F$2<$C8),REPT(CHAR(173),8),IF(AND(F$2<=TODAY(),F$2>$B8,$B8<>"",$C8=""),REPT(CHAR(173),7),""))))
F9:W9	F9	=IF(F$2=$B9,"s4",IF(F$2=$C9,"4s",IF(AND(F$2>$B9,F$2<$C9),4,IF(AND(F$2<=TODAY(),F$2>$B9,$B9<>""),4,""))))