Solved: How to unlink a TextBox linked to a cell in a worksheet

[limetoad]

I'm using Excel 2010, trying to filter through all TextBoxes in a worksheet and test if they are linked to a cell. Then, if they are linked I want to remove the link while keeping the text that was in the TextBox before unlinking it... This is the general idea:

For Each sh In ActiveSheet.Shapes
If Left([wherever the link address is stored], 1) = "=" Then [remove link]
Next sh

Any ideas?

[p45cal]

perhapsFor Each sh In ActiveSheet.TextBoxes
If Len(sh.Formula) > 1 Then sh.Formula = Empty
Next sh

[limetoad]

When I try that I get the following error: "Object doesn't support this property or method".

[snb]

for each it in activesheet.oleobjects
if it.object.linkedcell<>"" then it.object.linkedcell=""
next

[limetoad]

for each it in activesheet.oleobjects
if it.object.linkedcell<>"" then it.object.linkedcell=""
next

I tried something like this before, but as I'm F8'ing through the code it doesn't even read that one, as soon as it reads the first line it jumps over the rest of the for loop and continues with the rest of the code... Is there a reference that I need to add, or something else I am missing?

[snb]

You didn't tell us what kind of textboxes you are using (formcontrols, activeX controls, etc).
A sample workbook can do miracles....

[limetoad]

Good point snb, I actually have no idea what kind of textbox it is... So here is a sample of what I'm working with.

[limetoad]

It doesn't look like it let me attach the file, it said it was uploading, but it's not there... Anyways, the text box is whatever is the standard for Excel 2010... I just selected Insert>Text Box.

[p45cal]

It doesn't look like it let me attach the file, it said it was uploading, but it's not there... You have to have a minimum post count - don't know what it is (5?)






Anyways, the text box is whatever is the standard for Excel 2010... I just selected Insert>Text Box.I've just tried this in Excel 2010 and my code in Msg#2 works fine. Are you sure you've used ActiveSheet.TextBoxes in the first line?

[limetoad]

You have to have a minimum post count - don't know what it is (5?)





I've just tried this in Excel 2010 and my code in Msg#2 works fine. Are you sure you've used ActiveSheet.TextBoxes in the first line?
p45cal, you're right, I just had to use something other than "sh" for your code (see below), since I had already set "sh" as a Shape. Thanks!

Dim tb as Variant
For Each tb In ActiveSheet.TextBoxes
If Len(tb.Formula) > 1 Then tb.Formula = Empty
Next tb