| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Assign a text to a rectangle
I have a rectangle I made from the drawing tool bar and then I assigned a
macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a text to a rectangle
Hi Alberto
Since it is an autoshape you need to use code to do that..Try the below. Place this either in worksheet change event so that the text change will happen as soon as the text is changed... Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I have a rectangle I made from the drawing tool bar and then I assigned a macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a text to a rectangle
I am not sure where you say to put it but I did put it in the macro where I
change the value of the cell and it works great Thanks "Jacob Skaria" wrote: Hi Alberto Since it is an autoshape you need to use code to do that..Try the below. Place this either in worksheet change event so that the text change will happen as soon as the text is changed... Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I have a rectangle I made from the drawing tool bar and then I assigned a macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a text to a rectangle
Thanks for the feedback. Right click the sheet tabView code and paste the
below code and try changing the text in cell A1..The shape text will chage with this... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I am not sure where you say to put it but I did put it in the macro where I change the value of the cell and it works great Thanks "Jacob Skaria" wrote: Hi Alberto Since it is an autoshape you need to use code to do that..Try the below. Place this either in worksheet change event so that the text change will happen as soon as the text is changed... Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I have a rectangle I made from the drawing tool bar and then I assigned a macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a text to a rectangle
Yes but how will this work if the data and the rectangle are in different
sheets? "Jacob Skaria" wrote: Thanks for the feedback. Right click the sheet tabView code and paste the below code and try changing the text in cell A1..The shape text will chage with this... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I am not sure where you say to put it but I did put it in the macro where I change the value of the cell and it works great Thanks "Jacob Skaria" wrote: Hi Alberto Since it is an autoshape you need to use code to do that..Try the below. Place this either in worksheet change event so that the text change will happen as soon as the text is changed... Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I have a rectangle I made from the drawing tool bar and then I assigned a macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a text to a rectangle
Hi Alberto
Right click the sheet tab where your data is and View codePaste the code.. Change the sheet name mentioend in the code...I assume the shape is in Sheet3. and try changing the data.. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Dim sh As Shape Set sh = Worksheets("Sheet3").Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: Yes but how will this work if the data and the rectangle are in different sheets? "Jacob Skaria" wrote: Thanks for the feedback. Right click the sheet tabView code and paste the below code and try changing the text in cell A1..The shape text will chage with this... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I am not sure where you say to put it but I did put it in the macro where I change the value of the cell and it works great Thanks "Jacob Skaria" wrote: Hi Alberto Since it is an autoshape you need to use code to do that..Try the below. Place this either in worksheet change event so that the text change will happen as soon as the text is changed... Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I have a rectangle I made from the drawing tool bar and then I assigned a macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Assign a text to a rectangle
Thanks Jacob,
It worked great... you have helped me a lot today... well other times previously too but specially today... the closing enable command was a very good one. It is almost midnoght here so I have to stop now. AA "Jacob Skaria" wrote: Hi Alberto Right click the sheet tab where your data is and View codePaste the code.. Change the sheet name mentioend in the code...I assume the shape is in Sheet3. and try changing the data.. Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Dim sh As Shape Set sh = Worksheets("Sheet3").Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: Yes but how will this work if the data and the rectangle are in different sheets? "Jacob Skaria" wrote: Thanks for the feedback. Right click the sheet tabView code and paste the below code and try changing the text in cell A1..The shape text will chage with this... Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Not Application.Intersect(Target, Range("A1")) Is Nothing Then Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") End If Application.EnableEvents = True End Sub If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I am not sure where you say to put it but I did put it in the macro where I change the value of the cell and it works great Thanks "Jacob Skaria" wrote: Hi Alberto Since it is an autoshape you need to use code to do that..Try the below. Place this either in worksheet change event so that the text change will happen as soon as the text is changed... Dim sh As Shape Set sh = ActiveSheet.Shapes("Rectangle 1") sh.OLEFormat.Object.Text = Range("A1") If this post helps click Yes --------------- Jacob Skaria "Alberto Ast" wrote: I have a rectangle I made from the drawing tool bar and then I assigned a macro to it... can I change the text of the rectangle based on the string stored in a specific cell? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Text Box vs. rectangle, what is the difference? | Excel Discussion (Misc queries) | |||
| how do i assign a value to a cell containing text | Excel Worksheet Functions | |||
| Assign a Value if Some of the Text in 2 Cells are the Same | Excel Discussion (Misc queries) | |||
| assign value to text | Excel Worksheet Functions | |||
| assign a value to text | Excel Worksheet Functions | |||
![Alberto Ast[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
