Thread: Avoid changes of text properties when calling Shapes().Formula

Hello,

I'm working on a measurement automation VBA code on my enterprise. By now, I'm retrieving data from the acquisition, searching for values, doing some calculations and then storing them at some cells on a worksheet.

After that, a chart is duplicated from a template. On this chart a named textframe ("ResultadoPos") is used to show numeric results. My problem is with this code:

HojaGrafica.Activate
HojaGrafica.Shapes("ResultadoPos").Select
Selection.Formula = "='" + Libro.Worksheets(HojaDatos).Name & "'!R" & FilaPos & "C" & (ColumnaSalida + 3)

Where HojaGrafica is a chart sheet, ResultadoPos is a textframe, Libro a workbook, and HojaDatos the index number of the worksheet containing data

Code works, problem is text format from the template is changed with the text format of the cell I'm pointing to. By now I'm forcing formatting after doing this, but I really don't like it, as I want users to customize template as they want.

Is there any way to avoid this? I just want it to keep the template format changing the values shown in the textframe. If that's not possible, is there any way of copying all formating before doing the .Formula = and then pasting it again. I mean, something clean not something like storing all properties one by one and then applying them again.

Thanks in advance!

Xavi

Hi,

Let's see if this helps... Attached you'll find workbook with 1 sheet and 1 chart. It's a simple example of the behaviour I'm referring to.

Here's the code on the book, so most of you won't have to open the file to understand what I'm talking about. Everything except for the very last "Selection.Formula =" lacks interest it's just the example I came up with.

Sub SimpleTest()
    Dim X1, X2, Y1, Y2, line As Double
    Set WS = ThisWorkbook.Worksheets(1)
    Set Graf = ThisWorkbook.Charts(1)
    X1 = WS.Range("C2").Value
    X2 = WS.Range("C3").Value
    Y1 = WS.Range("D2").Value
    Y2 = WS.Range("D3").Value
    line = ((X2 - X1) ^ 2 + (Y2 - Y1) ^ 2) ^ 0.5
    WS.Range("D5").Value = line
    Graf.Shapes("Test").Select
    Selection.Formula = "='" & WS.Name & "'!R5C4"
End Sub

I'm just curious on two things.
1.- The previously asked "Why does Selection.Formula change the formatting of the text at "Test"?"
2.- Why do I get an error #438 if I do Graf.Shapes("Test").Formula = "whatever"?... Am I missing something? (This second point bugs me quite less than the first... What really bugs me is that it will be a pain saving all properties of "Test" and then recovering them again.

Thanks in advance!

Xavi

Thanks for your reply shg,

But that's not exactly the issue I'm facing. My problem is not with a formula on a cell changing the formatting of the cell but with a formula on a textframe changing the formatting of the textframe.

What I'm trying to achieve is:
- User changes chart design on a template (including text on the textframe that shows results).
- My VBA code does some searches & calcs and exports its result (as text + numbers) to a cell in the worksheet. (I have some other issues with this too at: link)
- At last I want my textframe to reference to the above mentioned cell. When I do this reference is when the text at the textframe takes formatting from the cell.

Hope this will make thinks clearer.