Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 97 keeps changing dimensions of autoshape rectangle | Excel Discussion (Misc queries) | |||
Assign a text to a rectangle | Excel Discussion (Misc queries) | |||
Rectangle shape round top corners only | Excel Discussion (Misc queries) | |||
Text Box vs. rectangle, what is the difference? | Excel Discussion (Misc queries) | |||
Add text to a rectangle in VBA | Excel Programming |