"linkedshapes" collection

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, gurus,
perhaps impossible question, but you never know :)

how can you find the "linked shapes" when you get a celladdress without looping through all shapes ?
in other words: does there exist a "linkedshapes" collection or something of the kind ?
VIRTUAL code which would give you all linked shapes
Code:
For Each sh in Range("A1").LinkedShapes
MsgBox sh.Name
next sh
of course looping solves the problem
this runs nicely
Code:
Option Explicit

Sub find_linked_shapes()
'Erik Van Geit
'051115
'finds all shapes in the same sheet linked to activecell

Dim sh As Shape
Dim LC As String
Dim CellAdd As String

CellAdd = ActiveCell.Address(0, 0)

    For Each sh In ActiveSheet.Shapes
        On Error Resume Next
        LC = ""
        LC = sh.OLEFormat.Object.LinkedCell
        LC = sh.ControlFormat.LinkedCell
        On Error GoTo 0
    If CellAdd = Application.WorksheetFunction.Substitute(LC, "$", "") Then MsgBox sh.Name
    Next sh

End Sub
kind regards,
Erik
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,697
Members
412,481
Latest member
nhantam
Top