Can't get SETATREF() to do this with external data (in shape data cell)

[Visisthebest]

David Parker wrote a very useful article about how any shapesheet cell can be updated with external data:

https://bvisual.net/2022/04/18/update-any-visio-shapesheet-cell-with-external-data/

It involves very simply:

"This can be fixed by entering a SETATREF(...) function into the Shape Data value cells whose values you want to re-direct to another cell."

Unfortunately this doesn't work when I do this, even though the data graphics work fine, refreshing the data works fine, but not on the Shape Data row I put the SETATREF() on, as per David's instructions:

"This can be fixed by entering a SETATREF(...) function into the Shape Data value cells whose values you want to re-direct to another cell. In this case the formulas =SETATREF(Width) and =SETATREF(Height), are entered into the Prop._visDM_Width.Value and Prop._visDM_Height.Value cells."

Any idea why this doesn't work? Thank you for sharing your insights!

[wapperdude]

Generally, you need to a more complete expression:  setatref(width,setatrefexpr(setatrefeval()))

In this case, you might be able to drop the setatrefexpr().  The data value will become inherited into the eval fcn upon execution, but you may want to "seed" it with some initial value.

See http://visguy.com/vgforum/index.php?topic=6383.0 for more indepth setatref() development.
See https://learn.microsoft.com/en-us/office/client-developer/visio/setatref-function for definition/description.

[Visisthebest]

I will try a more complete expression the simple solution doesn't work for data coming in from a data connection, if I change the data in the data source and refresh in Visio still nothing with the simple SETATREF expression.

[Croc]

Visisthebest, please upload a Visio file with a non-working example (or two files: Visio + data source). I would like to take a look at it.

[Croc]

This is the test result on my machine. Visio 2019.

[Visisthebest]

Croc thank you I am using SETATREF to set the text in a subshape, pushing a string in the data connection with SETATREF(Sheet.ID!TheText), that doesn't work unfortunately. (ID = Shape ID of the subshape)

[Croc]

I don't understand. The TheText cell is an event cell. How can you write data to it?

[Visisthebest]

With Sheet.ID!TheText or is that read-only?

[Croc]

TheText Cell (Events Section) 
An event cell that is evaluated when a shape's text or text composition changes.
Event cells are evaluated only when the event occurs, not upon formula entry. You can use the TheText cell to trigger recalculations, for example, to recalculate the text width and height with the TEXTWIDTH( ) and TEXTHEIGHT( ) functions.

[Visisthebest]

Thank you Croc my mistake, now I'm reading the user data cell with a formula in the text, and this works fine and updates well.

I am so used to reading/writing Shape.Text in VBA, that I forget it is not possible to write to Text with a shapesheet formula.

[Visisthebest]

If I create a User.Test row also it does not work (I get a 0 in User.Test not the text string). The issue is not confined to pushing data to TheText.

[Croc]

Try to use User.Test.Prompt

[wapperdude]

Couple things.  1st, setatref does not work with every shapesheet section.  See the 2nd link of post above.
2nd, technically, there is no "Text Cell", technically.  Or is there?!?  There is the Text Fields section of the shapesheet.  That ca n  be exploited.  See macro...

Code Select Expand


Sub addFieldTxt()
'tHIS MACRO ADDS TEXT TO A SHAPE WHERER NONE EXISTED.
'The desired text is stored i n variable MyTxt
'The code adds the Text Fields Section to the shapesheet, with desiredtext.
'If Text Field Section exists, then, no  need to add it, just nwrite to it.

    Dim shp As Visio.Shape
    Dim vChars1 As Visio.Characters
   
    MyTXT = "Some text"
   
    Set shp = ActiveWindow.Selection(1)
   
    If Not shp.SectionExists(visSectionTextField, False) Then
        Set vChars1 = shp.Characters
        vChars1.Begin = 0
        vChars1.End = 0
        vChars1.AddCustomFieldU Chr(34) & MyTXT & Chr(34), visFmtNumGenNoUnits
    End If
End Sub


[wapperdude]

To give an idea beyond the simple of the above text, there's this code:

Code Select Expand


Sub addFieldTxt()
    Dim shp As Visio.Shape
    Dim vChar1 As Visio.Characters
    Dim lstChr As Integer
   
    Set shp = ActiveWindow.Selection(1)
   
    If Not shp.SectionExists(visSectionTextField, False) Then
        shp.Characters.AddCustomFieldU Chr(34) & "NewFieldText" & Chr(34), visFmtNumGenNoUnits
       
'The next 2 sections show two methods of adding additional lines.
'Both lines show the same info:  the name of the shape.
'The 1st use text entry, the 2nd uses AddFieldEx

        lstChr = Len(shp.CellsSRC(visSectionTextField, 0, visFieldValue).ResultStr(visNone))
        Set vChar1 = shp.Characters
        vChar1.Begin = lstChr           'sets reference at end of string
        vChar1.End = lstChr
        vChar1.Text = "" & Chr(10) & "" 'sets new line
        vChar1.Begin = lstChr + 1       'sets reference at beginning of new line
        vChar1.End = lstChr + 1
        vChar1.Text = shp.Name          'adds shape name on the 2nd line as normal shape text
       
        lstChr = lstChr + 1 + Len(shp.Name)
        vChar1.Begin = lstChr           'increments to end of 2nd line
        vChar1.End = lstChr
        vChar1.Text = "" & Chr(10) & ""
        vChar1.Begin = lstChr + 1
        vChar1.End = lstChr + 1

'This next line adds the shape name as Field Text
        vChar1.AddFieldEx visFCatObject, visFCodeObjectName, visFmtStrNormal, 1033, 0
    End If
   
End Sub


[Visisthebest]

Thank you Wapperdude good to know what is possible with the shape text 'field'!