Update External Link in Text Box

Hey Group,

It has been a while since I got stuck and I need some help.

I use a template that has a tab "Cover Sheet" which has all of the author and project information.

In the working tabs, say "Sheet 1" we have a image as a type of header. On this header are text boxes that reference the "Cover Sheet" for particular pieces of information:
='Cover Sheet'!$U$34

Then when a new project comes, I start a new document from the template. Sometimes I will copy from other workbooks, worksheets that I need to the new document.

Unfortunately, the links in the text boxes refer back to the workbook that is was copied from.
='[OriginalDocument.xlsm]Cover Sheet'!$U$34

In the sheet I can use a find an replace to find "[OriginalDocument.xlsm]" and replace with "" and that will take care of links within the worksheets. However, this does not work for Text Boxes.

The only way that I know to find and replace in a text box is manually.

I am looking for advice on code that could be written that would search all text boxes and remove the exteral links. Would it be possible to find "[" and "]" and delete them with everthing in between to effectively remove the external link but leave the internal link intact?

Thanks! Rhodes

Forgot, if the file is not open, then the full path is also displayed. If that is the case, then deleting the [ ] and the information in between will not delete the path.
The code will need to be more discriminating. Hmmm....

First go thru the TextBoxes and remove all references, Ex:='Cover Sheet'!$U$34. Leave them blank or empty.


In the code module of the Template Sheet with Text Boxes add this code.

[VBA]
Private Sub WorkSheet_Activate()

TextBox1.Text = Sheets("Cover Sheet").Range("U34").Text 'Change T-Box name and Ranges as needed

TextBox2.Text = Sheets("Cover Sheet").Range("V35").Text

End Sub[/VBA]


That code will run in its entirety every time the text box sheet is activated. You can add a line to prevent it from running all the way through if textBox1 has some value. Put it as the top line under the Sub declaration.
[VBA]If TextBox1.Text <> "" Then Exit Sub[/VBA]

Is there a way to perfom this function if there are say 100 text boxes with unknown names and references?
I am looking to try and figure out the code to work without intervention.
I would for the code to look at all text boxes in the workbook and convert them to local references.

On an otherwise empty copy of the sheet with all the textboxes, in the sheets code module, put this code. be sure and set the constant FirstEmptyRow before running the sub. Post the result back here.

[vba]Option Explicit

Const FirstEmptyRow As Long = 1 'Set = Empty Row on sheet

Sub ListOLEControls()
Dim Ctrl As OLEObject
Dim i As Long
Dim r As Long

r = FirstEmptyRow
Cells(r, 1) = "Index"
Cells(r, 2) = "Name"
Cells(r, 3) = "LinkedCell"
Cells(r, 4) = "Visible"


For i = 1 To Me.OLEObjects.Count
r = r + 1
With Me.OLEObjects(i)
On Error Resume Next
Cells(r, 1) = i
Cells(r, 2) = .Name
Cells(r, 3) = .LinkedCell
Cells(r, 4) = .Visible
End With
Next i
End Sub
[/vba]

to make it faster:

[vba]
Sub ListOLEControls()
jj=1
with sheets("sheet1")
.Cells(1, 1).resize(,4) = array("Index","Name","LinkedCell","Visible")

redim sn(1 to oleobjects.count)
For each ob in OLEObjects
jj=jj+1
sn(jj,1)= jj-1
sn(jj,2) = ob.Object.Name
sn(jj,3) = ob.object.LinkedCell
sn(jj,4) = ob.object.Visible
Next

.cells(2,1).resize(ubound(sn),Ubound(sn,2))=sn
end With
End Sub
[/vba]

Gotta have an OERN for objects without LinkedCell property