Is there a way to check if an OLEObject has a LinkedCell property? For example labels and buttons 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.
+1
A:
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.
Lance Roberts
2010-10-21 01:05:52
This doesn't work.
xiaodai
2010-10-21 05:11:23
@xiaodai, actually I tested it to make sure it would work, when I got to work later today I'll show the proof. But really if you want help you need to give a little, like exactly how it doesn't work for you.
Lance Roberts
2010-10-21 14:46:11
@xiaodai, ok, I've posted the proof.
Lance Roberts
2010-10-21 18:58:54
Ok I will show you a proof that it doesn't work.
xiaodai
2010-10-22 03:20:05
@xiaodai, I not sure why you don't get it. You can't just say not it doesn't like a [Monty Python skit](http://www.youtube.com/watch?v=teMlv3ripSM). You need to go over __exactly__ what it is doing wrong so we can determine why. As a programmer you need to get good at troubleshooting. You have to fight to figure out what's wrong.
Lance Roberts
2010-10-22 17:09:05
Dude. I am at home. I will show you. Ok? I am genuine. I tried your solution and I found instances where it doesn't work. Trust me.
xiaodai
2010-10-23 04:09:23
@xiaodai. You don't have to convince me, I'm sure it doesn't work for you. I'm saying you need to be specific about what's going on to find out why.
Lance Roberts
2010-10-23 04:42:11
ok this works. I was experimenting with Shapes. Essential I want to relink all the shape's objects too. I ended up writing some more advanced error trapping code to make it work. But your solution works for the original problem that I posed. Thanks. My solution is belwo
xiaodai
2010-10-27 04:15:30
A:
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:
xiaodai
2010-10-27 04:16:46