Visio Plan 2: Populate Comment with Data Value

Question

Edit:

I figured out that it selects the connector lines as shapes and since there is no data element called "Definition" for the connector lines, it gives the error. My quick solution was to define the data Element for the connector lines and now the macro works.

Can someone help me to exclude the connector lines?

Original post below:

I've created a basic flowchart by importing data from Excel. I now want to set the ScreenTip = the Definition data element but am not successful.

This is what the data looks like

This is the VBA code

Sub PopulateCommentWithDataElement()

Dim currentPage As Page 

Dim currentShape As Shape 

Dim dataElement As String 

' Specify the data element to populate the Comment field with 

dataElement = "Definition" 

' Get the reference to the current page 

Set currentPage = ActiveWindow.Page 

' Loop through each shape on the current page 

For Each currentShape In currentPage.Shapes 

    ' Check if the shape has the Comment field 

    If currentShape.CellExistsU("Comment", visExistsAnywhere) Then 

        ' Set the value of the Comment field to the data element value 

        currentShape.CellsU("Comment").FormulaU = "=Prop." & dataElement 

    End If 

Next currentShape 

' Clean up the objects 

Set currentShape = Nothing 

Set currentPage = Nothing 

End Sub

The error is

on this line

currentShape.CellsU("Comment").FormulaU = "=Prop." & dataElement

Another website suggested adding more quotes (currentShape.CellsU("Comment").FormulaU = """" & "=Prop." & dataElement & """") but then it populates every single Comment with "Prop.Definition". I get this. But removing the quotes gives the #NAME error.

I will appreciate some help. PS I'm not a VBA boffin, found the code with the help of ChatGPT but it needs a real human to resolve! :-)

Answer 1

Thanks Sean noted.

Answer 2

Dear MJ Fick,

Thanks for your reply,

Please feel free to post in Microsoft community again. We also welcome you to share your experience and suggestions here, this could help other members with the same concern.

Have a nice day and keep safe.

Sincerely,

Sean | Microsoft Community Moderator

Answer 3

Thanks Sean

Answer 4

Dear MJ Fick,

Thanks for posting in Microsoft Community.

We would love to help you on your query about VBA code, however, our team focuses on general query, for example, installation and activation issue of Office 365 products. The situation you mentioned is related to VBA code, you can to refer to this article: Office VBA support and feedback | Microsoft Docs to go to Stack Overflow by using the VBA tag, along with any other relevant tags as there are also many experienced engineers and experts in the forums there. 

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites, or any support related to technology.

At the same time, we will also keep this thread open, so other Community members and Experts can also share their suggestions and inputs. 

Thank you for your understanding and cooperation! Have a great day.

Sincerely,

Sean | Microsoft Community Moderator