CODEWITHSUNDEEP
vbavisio
Mark Di Val
Mark Di Val

Reputation: 81

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

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.

Upvotes: 2

Views: 4468

Answers (1)

L8n
L8n

Reputation: 728

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

Upvotes: 1

Related Questions