"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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,808
Messages
5,833,781
Members
430,232
Latest member
Testsubject

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top