In VBA how do I check if an OLEObject has a LinkedCell property?

Is there a way to check if an OLEObject has a LinkedCell property? For example labels and button开发者_运维技巧s don't have linkedcells while others do. I am trying to write a piece of code that can replace the linkedCells by looping through all the OLEObjects in a sheet. Thanks in advance.

You have to do the standard VBA technique of catching an error to test the LinkedCell property.

Public Sub test()

Dim cntl As Object
On Error Resume Next

For Each cntl In ActiveSheet.OLEObjects
Debug.Print cntl.Name

If IsError(cntl.LinkedCell) Then
    Debug.Print "No Linked Cell"
Else
    Debug.Print "Linked Cell"
End If

Next cntl

End Sub

The following is the picture of proof that it worked, on a blank Excel sheet, with four different controls.

    For Each tempWk In trunkWb.Sheets
            For Each tempShape In tempWk.Shapes

            Set obj = tempShape.OLEFormat.Object

            'this bit of code can be confusing but it's necessary
            On Error GoTo LinkedCellNotValid
            With Application.WorksheetFunction
                obj.LinkedCell = .Substitute(obj.LinkedCell, "[" & branchWb.Name & "]", "")

                For j = 1 To i
                    Set oldwk = trunkWb.Worksheets(sheetNames(j - 1))
                    obj.LinkedCell = .Substitute(obj.LinkedCell, "_review_" & j, oldwk.Name)
                Next j
            End With
            GoTo LinkedCellDone
LinkedCellNotValid:
            'clear the error
            Err.Clear
            Resume LinkedCellDone

LinkedCellDone: