Knowing Name of Shape with VBA

[prajul89]

Hi,
How to know the name of the Shapes (Rectangle) in a particular range with macro.

 

[Dannyh1]

Hi - could record macro & select the shape, code will then tell you what you the name

 

[Rick Rothstein]

Not exactly sure what you are after, but see if you can make use of one of these (see the macro's name)...

Sub GetShapesWhoseSurroundingRectangleTouchesTheSelectedRangeSomewhere()
  Dim SH As Shape, ShapesInRange As String
  For Each SH In ActiveSheet.Shapes
    If Not Intersect(Range(SH.TopLeftCell.Address & ":" & SH.BottomRightCell.Address), Selection) Is Nothing Then
      ShapesInRange = ShapesInRange & SH.Name & vbLf
    End If
  Next
  If Len(ShapesInRange) Then
    MsgBox ShapesInRange
  Else
    MsgBox "There are no shapes in that range!"
  End If
End Sub
 
Sub GetShapesThatAreCompletelyWithinTheSelectedRangeOfCells()
  Dim SH As Shape, ShapesInRange As String, Intersection As Range
  For Each SH In ActiveSheet.Shapes
    Set Intersection = Intersect(Range(SH.TopLeftCell.Address & ":" & SH.BottomRightCell.Address), Selection)
    If Not Intersection Is Nothing Then
      If Intersection.Address = Range(SH.TopLeftCell.Address & ":" & SH.BottomRightCell.Address).Address Then
        ShapesInRange = ShapesInRange & SH.Name & vbLf
      End If
    End If
  Next
  If Len(ShapesInRange) Then
    MsgBox ShapesInRange
  Else
    MsgBox "There are no shapes in that range!"
  End If
End Sub

Note that each macro will list all the shapes that meet the condition the macro is looking for (again, read each macro's name to see what that is).

 

[shg]

One way:

Sub x()
    Dim shp As Shape
    
    For Each shp In ActiveSheet.Shapes
        If Not Intersect(shp.TopLeftCell, Range("A1:A10")) Is Nothing Then
            shp.Select
            MsgBox shp.Name
        End If
    Next shp
End Sub

 

[prajul89]

Thanks a lot, that helps a lot!