Changing text in a rectangle (shape) using a macro
 

Hello,

I created a rectangle on Sheet1 and named it txtClaim. It links to cell
Sheet1!A1 that has a value of "10-yr claim rate = 10%". The idea is that
everytime Sheet1!A1 is updated, the text in txtClaim is also updated
accordingly. Now, I want to make a copy of Sheet1 onto a new workbook. The
new txtClaim now links to [worksbook1.xls]Sheet1!A1. What I'd like to do is
to break the link and retain the text "10-yr claim rate = 10%", so the new
txtClaim does not depend on the original workbook anymore. How can I do it
programatically?

Please help,
Adrian T

Changing text in a rectangle (shape) using a macro
 

Adrian,

With the copy active:

Sub BreakLink()
ActiveSheet.Shapes("txtClaim").Select
ExecuteExcel4Macro "FORMULA("""")"
End Sub

HTH,
Bernie
MS Excel MVP


"Adrian T" wrote in message
...
Hello,

I created a rectangle on Sheet1 and named it txtClaim. It links to cell
Sheet1!A1 that has a value of "10-yr claim rate = 10%". The idea is that
everytime Sheet1!A1 is updated, the text in txtClaim is also updated
accordingly. Now, I want to make a copy of Sheet1 onto a new workbook. The
new txtClaim now links to [worksbook1.xls]Sheet1!A1. What I'd like to do is
to break the link and retain the text "10-yr claim rate = 10%", so the new
txtClaim does not depend on the original workbook anymore. How can I do it
programatically?

Please help,
Adrian T

Changing text in a rectangle (shape) using a macro
 

Thank you. Any idea other than selecting? I am not too fond of using select
if possible.

"Bernie Deitrick" wrote:

Adrian,

With the copy active:

Sub BreakLink()
ActiveSheet.Shapes("txtClaim").Select
ExecuteExcel4Macro "FORMULA("""")"
End Sub

HTH,
Bernie
MS Excel MVP


"Adrian T" wrote in message
...
Hello,

I created a rectangle on Sheet1 and named it txtClaim. It links to cell
Sheet1!A1 that has a value of "10-yr claim rate = 10%". The idea is that
everytime Sheet1!A1 is updated, the text in txtClaim is also updated
accordingly. Now, I want to make a copy of Sheet1 onto a new workbook. The
new txtClaim now links to [worksbook1.xls]Sheet1!A1. What I'd like to do is
to break the link and retain the text "10-yr claim rate = 10%", so the new
txtClaim does not depend on the original workbook anymore. How can I do it
programatically?

Please help,
Adrian T

Changing text in a rectangle (shape) using a macro
 

Adrian,

Sorry, but I think that's one of those cases where you have to select. I hate running the Excel4
macros, too, but the link is problematic. AFAIK. If you had event code that changed the rectangles
text based on a cell change event, then it would be easy to do without selecting, since there would
be no link to mess around with...

HTH,
Bernie
MS Excel MVP


"Adrian T" wrote in message
...
Thank you. Any idea other than selecting? I am not too fond of using select
if possible.

"Bernie Deitrick" wrote:

Adrian,

With the copy active:

Sub BreakLink()
ActiveSheet.Shapes("txtClaim").Select
ExecuteExcel4Macro "FORMULA("""")"
End Sub

HTH,
Bernie
MS Excel MVP


"Adrian T" wrote in message
...
Hello,

I created a rectangle on Sheet1 and named it txtClaim. It links to cell
Sheet1!A1 that has a value of "10-yr claim rate = 10%". The idea is that
everytime Sheet1!A1 is updated, the text in txtClaim is also updated
accordingly. Now, I want to make a copy of Sheet1 onto a new workbook. The
new txtClaim now links to [worksbook1.xls]Sheet1!A1. What I'd like to do is
to break the link and retain the text "10-yr claim rate = 10%", so the new
txtClaim does not depend on the original workbook anymore. How can I do it
programatically?

Please help,
Adrian T

Changing text in a rectangle (shape) using a macro
 

I set up a worksheet and added a rectangle from the Drawing toolbar. I selected
it and then typed =a1 in the formula bar.

When I copied that sheet (edit|move or copy sheet), the rectangle on the new
sheet pointed to A1 of that new sheet (in the new workbook.

So I couldn't replicate your situation.

But this did work to change the formula to B1 of the new sheet:

Dim wks As Worksheet
Set wks = ActiveSheet

With wks
.Rectangles(1).Formula = "=B1"
End With

And this worked to remove the formula

Option Explicit
Sub testme01()
Dim wks As Worksheet
Set wks = ActiveSheet

wks.Copy 'to a new workbook

Set wks = ActiveSheet 'the new worksheet

With wks
.Rectangles(1).Formula = ""
End With

End Sub



Adrian T wrote:

Hello,

I created a rectangle on Sheet1 and named it txtClaim. It links to cell
Sheet1!A1 that has a value of "10-yr claim rate = 10%". The idea is that
everytime Sheet1!A1 is updated, the text in txtClaim is also updated
accordingly. Now, I want to make a copy of Sheet1 onto a new workbook. The
new txtClaim now links to [worksbook1.xls]Sheet1!A1. What I'd like to do is
to break the link and retain the text "10-yr claim rate = 10%", so the new
txtClaim does not depend on the original workbook anymore. How can I do it
programatically?

Please help,
Adrian T

--

Dave Peterson