Identify Shape Name by it's location

[Plotweaver]

I need to identify a shape's name by knowing only its top and left location data.

It is obviously possible to find a selected shape's location and name.: (Example 1: find shapes.)

Dim activeshape As Shape, UserSelection As Variant
Set UserSelection = ActiveWindow.Selection
On Error GoTo NoShapeSelected
Set activeshape = ActiveSheet.Shapes(UserSelection.Name)
On Error Resume Next
MsgBox "You have selected:" & activeshape.Name & "; Top: " & activeshape.top & "; Left: " & activeshape.left
Exit Sub
NoShapeSelected:
MsgBox "You do not have a shape selected!"

But is there a way to find the name of a shape with only its location known?: (Example 2: find shape name)

Set shp = Application.ActiveSheet.Shapes(name)
shp.top = 84
shp.left = 114
Range("A1") = name

Thank you for any information you can offer to resolve this.

 

[Jaafar Tribak]

How do you find out the location of the shape ? Do you have the Left & Top values of each shape stored somewhere ?

 

[Plotweaver]

I don't understand. Why are you sending me to a facebook page? This has nothing to do with my Excel problem.

 

[Jaafar Tribak]

I don't understand. Why are you sending me to a facebook page? This has nothing to do with my Excel problem.

That wasn't my answer. You must have clicked by mistake on my forum signature ?

I was asking you how you know the left and top values of each shape on the worksheet ?

 

[BSALV]

as Jaafar explains, with Shapes("MyShape").topleftcell.address

 

[Plotweaver]

All shapes are placed at regular intervals, so I know where they are, but not necessaryily which one it is.

 

[Plotweaver]

as Jaafar explains, with Shapes("MyShape").topleftcell.address

This looks like it might work. Using my example, with known values of top = 84, left = 114, how whould I complete the code to determine its name? Thanks.

 

[BSALV]

if there aren't that much shapes

Sub left_top()
     For Each shp In ActiveSheet.Shapes
          s = shp.Name & " " & shp.TopLeftCell.Address & "  " & shp.Left & "-" & shp.Top
          If shp.Left = 114 And shp.Top = 84 Then
               MsgBox "your shape is " & s, vbInformation, "FOUND": Exit Sub     'teh right top and left
          Else
               s0 = s0 & vbLf & s                               'collect all shapes
          End If
     Next
     MsgBox Mid(s0, 2), vbQuestion, "All shapes"
End Sub

 

[Jaafar Tribak]

I would use a function like this :

In a Standard Module:

Public Function ShapeFromLocation( _
        ByVal X As Single, _
        ByVal Y As Single, _
        Optional ByVal PerentSheet As Worksheet _
    ) As Shape
   
    Dim Shp As Shape
   
    If PerentSheet Is Nothing Then Set PerentSheet = ActiveSheet
   
    For Each Shp In PerentSheet.Shapes
        If Shp.Left = X And Shp.Top = Y Then
            Set ShapeFromLocation = Shp
            Exit For
        End If
    Next Shp

End Function

And you call the function like this :

Sub Test()

    Dim Shp As Shape
   
    Set Shp = ShapeFromLocation(X:=114, Y:=84, PerentSheet:=ActiveSheet)
    If Not Shp Is Nothing Then
        MsgBox "Your shape is : '" & Shp.Name & "'"
    Else
        MsgBox "No shape found!"
    End If

End Sub

 

[Plotweaver]

if there aren't that much shapes

Sub left_top()
     For Each shp In ActiveSheet.Shapes
          s = shp.Name & " " & shp.TopLeftCell.Address & "  " & shp.Left & "-" & shp.Top
          If shp.Left = 114 And shp.Top = 84 Then
               MsgBox "your shape is " & s, vbInformation, "FOUND": Exit Sub     'teh right top and left
          Else
               s0 = s0 & vbLf & s                               'collect all shapes
          End If
     Next
     MsgBox Mid(s0, 2), vbQuestion, "All shapes"
End Sub

This should do the trick. While I was trying to avoid the 'if then' method, I guess going through each shape is the only way to 'find' it. Thanks.