In Excel 2010 when I change the formula of all shapes on the active sheet to be "=A1", then he format (font, color, etc) of the shapes ALSO change from whatever they were set to to be the same as the format of cell A1. Why is this? How can you just change the formula of the shape without the shape's format also changing to that of the cell referenced by the formula.


Sub FormApp()
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
'formula
Shp.DrawingObject.Formula = "=A1"
Next
End Sub

Hi

Can you try if the following works

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

I tried this suggestion but it made no difference. The format of the shape changed to that of the A1 cell.

Can I please confirm what your experience is ... that is if you create an Excel 2010 workbook and you create a shape, with a particular format and then you add a formula to the shape to reference the value of another cell whose format is different to the shape's format, does your shape's format change to that of the cell that the formula is targeting, or does it remain unchanged?

Hi

The format gets aligned to the pointed cell. Using.caption doesn't change the formating but this is not dynamic

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

May be something like this is running in the background?

http://www.mrexcel.com/forum/excel-q...e-changes.html

I have had advice through another forum post that this behaviour is normal for Excel and its shapes. When you change the formula of a shape it changes the format of the shape to that of the cell that the formula is referring to. I had been advised by a third party software development company that I acquired a tool from that this was not the case for at least e2010. It later turned out that the version of e2010 that they were using was a pre-release version and when they upgraded to SP2 they experienced the same behaviour.

Forum post closed. http://answers.microsoft.com/en-us/o...=1415099885822

This thread can now be closed from my point of view.