Move shape to a specific column based on a date text value

[Anthrol]

Hi All,

I've dabbled and I'm currently working on an onboarding sheet with chevron shapes placed at different dates in a calendar. I have one chevron that always needs to be placed below the April 9th column. If I change the date on one sheet it updates all the dates in the calendar sheet I created, and I want the chevron to move automatically horizontally with that one date in the calendar. My macro code is below,

Sub Check()
Dim rng As Range
Set rng = Sheets("Calendar").Range("G5:FI5")
For Each Cell In rng
If Cell.text = "9-Apr" Then
ActiveSheet.Shapes("45_days_training").Left = rng.Left
End If
Next
End Sub

The code is very simple and the problem line seems to be the ActiveSheet.Shapes line. I need to specify the cell text "Apr-9" as the value I want the chevron to move to. I can't seem to work out what the line should say to stick with that date value along the 5th row.

Thanks,

Simon

 

[lrobbo314]

Welcome to the forum. This code seems to do the trick. You might have to change the name of the shape that's on your sheet.

Sub Check()
Dim shp As Shape
Dim r As Range, cel As Range


Set shp = ActiveSheet.Shapes("Chevron 1") 'Shape name to change
Set r = Range("G5:FI5")


For Each cel In r
    If cel.Text = "9-Apr" Then
        shp.Left = cel.Left - shp.Width
        shp.Top = cel.Top - (shp.Height / 2) + (cel.Height / 2)
        Exit For
    End If
Next cel


End Sub

 

[Anthrol]

Yes! With a slight adjustment, it worked Thanks lrobbo314!!

Welcome to the forum. This code seems to do the trick. You might have to change the name of the shape that's on your sheet.

Sub Check()
Dim shp As Shape
Dim r As Range, cel As Range


Set shp = ActiveSheet.Shapes("Chevron 1") 'Shape name to change
Set r = Range("G5:FI5")


For Each cel In r
    If cel.Text = "9-Apr" Then
        shp.Left = cel.Left - shp.Width
        shp.Top = cel.Top - (shp.Height / 2) + (cel.Height / 2)
        Exit For
    End If
Next cel


End Sub

 

[lrobbo314]

[FONT=&quot]´´´´´´´´´´´´´´´´´´´´´´¶¶¶¶¶¶¶¶¶……..[/FONT]
[FONT=&quot]´´´´´´´´´´´´´´´´´´´´¶¶´´´´´´´´´´¶¶……[/FONT]
[FONT=&quot]´´´´´´¶¶¶¶¶´´´´´´´¶¶´´´´´´´´´´´´´´¶¶……….[/FONT]
[FONT=&quot]´´´´´¶´´´´´¶´´´´¶¶´´´´´¶¶´´´´¶¶´´´´´¶¶…………..[/FONT]
[FONT=&quot]´´´´´¶´´´´´¶´´´¶¶´´´´´´¶¶´´´´¶¶´´´´´´´¶¶…..[/FONT]
[FONT=&quot]´´´´´¶´´´´¶´´¶¶´´´´´´´´¶¶´´´´¶¶´´´´´´´´¶¶…..[/FONT]
[FONT=&quot]´´´´´´¶´´´¶´´´¶´´´´´´´´´´´´´´´´´´´´´´´´´¶¶….[/FONT]
[FONT=&quot]´´´´¶¶¶¶¶¶¶¶¶¶¶¶´´´´´´´´´´´´´´´´´´´´´´´´¶¶….[/FONT]
[FONT=&quot]´´´¶´´´´´´´´´´´´¶´¶¶´´´´´´´´´´´´´¶¶´´´´´¶¶….[/FONT]
[FONT=&quot]´´¶¶´´´´´´´´´´´´¶´´¶¶´´´´´´´´´´´´¶¶´´´´´¶¶….[/FONT]
[FONT=&quot]´¶¶´´´¶¶¶¶¶¶¶¶¶¶¶´´´´¶¶´´´´´´´´¶¶´´´´´´´¶¶…[/FONT]
[FONT=&quot]´¶´´´´´´´´´´´´´´´¶´´´´´¶¶¶¶¶¶¶´´´´´´´´´¶¶….[/FONT]
[FONT=&quot]´¶¶´´´´´´´´´´´´´´¶´´´´´´´´´´´´´´´´´´´´¶¶…..[/FONT]
[FONT=&quot]´´¶´´´¶¶¶¶¶¶¶¶¶¶¶¶´´´´´´´´´´´´´´´´´´´¶¶….[/FONT]
[FONT=&quot]´´¶¶´´´´´´´´´´´¶´´¶¶´´´´´´´´´´´´´´´´¶¶….[/FONT]
[FONT=&quot]´´´¶¶¶¶¶¶¶¶¶¶¶¶´´´´´¶¶´´´´´´´´´´´´¶¶…..[/FONT]
[FONT=&quot]´´´´´´´´´´´´´´´´´´´´´´´¶¶¶¶¶¶¶¶¶¶¶…….[/FONT]