2

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.

Mark Di Val
  • 81
  • 1
  • 7
  • Try use syntax. CustomVariable.Formula = "TheDoc!User.Revision" – Surrogate Aug 05 '18 at 22:06
  • 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 Aug 07 '18 at 00:06
  • 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 Aug 07 '18 at 02:44
  • I don't understand where you use this syntax. For shape's or document's level ? – Surrogate Aug 08 '18 at 21:28
  • 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 Aug 08 '18 at 22:10
  • you have multipage document with shapes with fields contain revision number on each page ? – Surrogate Aug 14 '18 at 11:21
  • I have a multi-page Visio document. I added one row in the User-defined Vells in the document shape sheet and called it "Revision". On each sheet in the document I have inserted a field that links to the User.Revision value (formula). – Mark Di Val Aug 14 '18 at 12:27
  • To set the value of this row from a macro I first need to find the row in a reliable way by iterating through the User-defined Cells section of the document shape sheet until I find a row named "Revision". I'll get there somehow - but any advice would be appreciated. There seems to bed not much info around Visio macros. – Mark Di Val Aug 14 '18 at 12:32

1 Answers1

1

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
L8n
  • 728
  • 1
  • 5
  • 15
  • I tried this code - fixing a typo or two - but invariably I get this error. "Run-time error '-2032466967 (86db03e9)'. Unexpected end of file." – Mark Di Val Sep 09 '19 at 04:26
  • This usually happens if you try to access a cell that does not exist. That's what "PrepareCellOndocumentSheet" is for, it creates the row/cell if it not exists, just call it right before you try to read/write to the sheet – L8n Sep 09 '19 at 07:00
  • Typos fixed an code changed so it runs in a code module, just copy the whole thing to test it. – L8n Sep 09 '19 at 07:06