Set user-defined cell value in Visio document shape sheet from a macro.

Question

I added a row to the User-defined Cells section of the main Visio document shape sheet called User.Revision. I can use the value in the drawings by adding a field set to use a Custom Formula =TheDoc!User.Revision.

I would like to have a macro to set this Cell value but I can't find a way to reference the User-defined Cells in VBA. TheDoc!User.Revision doesn't work.

Try use syntax. CustomVariable.Formula = "TheDoc!User.Revision" — Surrogate
I'm not doing it right. Sub SetRevision() Dim Message, Title, Default, MyValue Message = "Please enter the new revision marker." Title = "Revision" Default = CustomVariable.Formula = "TheDoc!User.Revision" ' This doesn't work ... MyValue = InputBox(Message, Title, Default) TheDoc!User.Revision = MyValue 'This doesn't work either but it doesnt _ get that far anyway ... End Sub — Mark Di Val
I recorded a Macro to add a row in the User-defined cells section. I looked at how the row was inserted and how the formula (value) was referenced. It uses this syntax: Application.ActiveWindow.Shape.CellsSRC(visSectionUser, 1, visUserValue).FormulaU This works because the added row is the second row i.e. index of 1. I Will have to iterate through to find the index of my row - where RowNameU = "Revision" if I want it to be reusable and robust. — Mark Di Val
I don't understand where you use this syntax. For shape's or document's level ? — Surrogate
Document level. I am using a User-defined Cell in the document shape sheet as the file revision. I place a field on each drawing so when it it printed or viewed I can tell which revision it is. I wanted an easy way to update the User-defined Cell so I don't have to open the document shape sheet every time because for some reason that is very slow. — Mark Di Val

L8n L8n · Accepted Answer · 2019-09-02T15:51:02

So this is a bit late, but I guess someone may still be able to use this.

The ShapeSheet of the document is called DocumentSheet and can be accessed like this:

Sub testReadWriteDocumentSheet()

    Dim someDoc As Visio.Document
    Set someDoc = ThisDocument 'or whatever other document you need

    Dim rowName As String 'the Name of the Row
    rowName = "ExampleRevision"

    Dim value As String 'the value you want to store, change to whatever format you need.
    value = "132ABC"

    'to set up the cell:
    PrepareCellOnDocumentSheet someDoc, rowName

    'to write
    someDoc.DocumentSheet.CellsU("User." & rowName).FormulaForceU = Chr$(34) & value & Chr$(34)
    'Chr$(34) is needed to add the quotes if you store a string,
    'if you store a Double or Long leave them out.

    'to read:
    Dim returnValue As String
    returnValue = someDoc.DocumentSheet.CellsU("User." & rowName).ResultStr(visNoCast)
    'returns the string but without the quotes
    Debug.Print returnValue

End Sub

Bonus: This code automatically checks if the row (and User-Section) you ask for exists and if not adds them.

Private Function PrepareCellOnDocumentSheet(ByVal someDoc as Visio.Document, ByVal rowName As String)
    With someDoc.DocumentSheet
        If Not .SectionExists(visSectionUser, False) Then
            .AddSection visSectionUser
        End If
        If Not .CellExistsU("User." & rowName, True) Then
            .AddNamedRow visSectionUser, rowName, visTagDefault
        End If
    End With
End Function

Set user-defined cell value in Visio document shape sheet from a macro.

1 Answers