MrRhodes2004
05-16-2013, 08:20 AM
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
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