VBA - create a shape always in top left corner of worksheet

[excelstarter1]

Hey guys,

I got a tricky problem - well probably not for you, but for me

I have a standard presentation design with standardized table size and graphes.

In order to check whether my Excel tables and graphs have the exact size for the presentation (so I dont need to resize/shrink them) I created a rectangle shape with VBA which has the exact size for the presentation. So I just move it over my table/graph an check whether it fits the standard or not. Currently the rectangle shape always starts in the top left corner of my worksheet (so basically A1). However when I move down several rows, I dont want the shape to be shown in cell A1 but, depending on where I am in the worksheet in cell F400 (top left corner).

Is there a way in VBA to create a shape always in the top left corner of my screen, so the shape can be created dynamically?

Thanks in advance!

 

[MickG]

This code will place a previously created triangle at the top left of the screen whenever the sheet is clicked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.Shapes("Isosceles Triangle 1")
    .Top = Cells(ActiveWindow.ScrollRow, 1).Top
    .Left = Cells(ActiveWindow.ScrollRow, 1).Left
End With
End Sub

 

[excelstarter1]

MickG, thanks for your quick reply and code!

It works with regard to the rows, however when I move over several columns, the shape does not "follow", i.e. it is stuck in column A. How would you suggest to adjust the code?

And I was also wondering in the case that I don't want to move the shape exactly in the top left corner but with a space in between - so for example still top left corner but 5cm lower from the top and 5cm from the left?

 

[MickG]

Try this:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet.Shapes("Isosceles Triangle 1")
    .Top = Cells(ActiveWindow.ScrollRow, 1).Top + 10
    .Left = Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Left + 10
End With
End Sub

 

[excelstarter1]

Awesome, thank you so much for your quick response and help!
It works perfectly!
Regards

 

[MickG]

You're welcome