Fill a property on shapes by vba code

Hey guys, Im on a new project in my company and I need to make some things in Visio 2016.

I want to write a vba code that create a property in shape data and then fill it with the container's shape name. Is there possible?

Ive already wrote a code that creat a property "Setor" in all shapes on the page, but I need to fill this property with the name of a container the shapes are into.

The code that Im using is this:

Private Sub Setorizacao()

Dim visShape As Visio.shape
Dim visPage  As Visio.Page

Set visPage = ActivePage

For Each visShape In ActivePage.Shapes
   If visShape.CellExists("LockWidth", False) Then
   Call InitSetor(visShape)
   End If
End Sub

Sub InitSetor(ByRef shape As Visio.shape)

' Create Prop.Setor if they don't exist; set both fields to null

    If Not shape.CellExistsU("Prop.Setor", False) Then
        shape.AddNamedRow visSectionProp, "Setor", visTagDefault
    End If
    shape.CellsU("Prop.Setor").FormulaU = ""
    shape.CellsU("Prop.Setor.Label").FormulaU = Chr(34) & "Setor" & Chr(34)
End Sub

Thank you in advance.
Lucas TarettiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
Doesn't your code already set the value of the property to 'Setor'?

If it does and you want to change 'Setor' to the name of the shape's container perhaps you could try something like this.
shape.CellsU("Prop.Setor.Label").FormulaU = Chr(34) & shape.Parent.Name & Chr(34)

Open in new window

Lucas TarettiAuthor Commented:
Thanks for ur response.

My code just add a prop row named "Setor" on shape data. The property is created with no value, it stays blank.

I add this line in my code but returns me an error "object required".
NorieAnalyst Assistant Commented:

I'm a bit rusty with Visio VBA but assumed the Parent property of the shape object would be its 'container'.

Do you still get the same error with this?
MsgBox shape.Parent.Name

Open in new window

OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Lucas TarettiAuthor Commented:
Same error Norie.

I think you didnt understand my problem, I'll try to explain it better.

My code insert a prop "Setor" in all shapes on the page, but all those shapes are in two different containers "SETOR 1" and "SETOR 2", so I want to fill the prop "Setor" with the container's name.
Shapes in container "SETOR 1" fill the prop "Setor" with "SETOR 1"
Shapes in container "SETOR 2" fill the prop "Setor" with "SETOR 2"

I changed the container's shape name in developer tab to become more easy to refer their names on the code.
NorieAnalyst Assistant Commented:

I think I understand the problem but perhaps misinterpreted the code on first look and/or misunderstood when you want to do this.

Is this code setting the label for the property you've created?
    shape.CellsU("Prop.Setor.Label").FormulaU = Chr(34) & "Setor" & Chr(34)

Open in new window


pls try (if the container is a shape)
shape.CellsU("Prop.Setor.Label").FormulaU = Chr(34) & shape.ContainingShape.Name & Chr(34)

Open in new window

Lucas TarettiAuthor Commented:
This part of the code sets the name of the prop on shape data.

If I change the "Setor" to "Shape" for example, the prop will be named Shape instead of Setor.

The first part of the code, search the "LockWidth" field in all shapes and if this field exists, then the prop will be created.

The second part is the "machine" that really creats the prop on shape data, I call the second part in the first one.
shape.CellsU("Prop.Setor").FormulaU = Chr(34) & shape.ContainingShape.Name & Chr(34)

Open in new window

Lucas TarettiAuthor Commented:
Rgonzo, thanks for ur response.

I changed the code and the prop fills with "ThePage". I need the prop fills with the container's name
then try
shape.CellsU("Prop.Setor").FormulaU = Chr(34) & shape.ContainingPage.Name & Chr(34)

Open in new window

shape.CellsU("Prop.Setor").FormulaU = Chr(34) & shape.ContainingPage.NameU & Chr(34)

Open in new window

Lucas TarettiAuthor Commented:
It still isnt working.

If I put, returns "ThePage" or "Page-1".
Scott HelmersVisio Consultant, Trainer, Author, and DeveloperCommented:
A question and a suggestion:

Question: is there a reason why you're using VBA to add the shape data field and formula? Usually a better alternative is to build a master or two that already contain the fields you need, including embedded formulas, if necessary. Then put the masters into a custom stencil and the shapes do the right thing automatically when you drag them onto a page.

Suggestion: You don't actually need VBA code for a shape to know what container it's in; there are ShapeSheet formula that do this work for you -- but you also need to know a bit about how containers are constructed. Here's the short version:

The header in a container is a subshape of the container shape and the container can 'read' the text from the header by getting a reference to the subshape's text. In the attached example, the container is Sheet.3 and its header is Sheet.6. Consequently, I created a User field like this:
Then, in the contained shape, the Shape Data field Value cell contains this formula:contained shape
BTW, IFERROR() is just a convenience. By using this function, if the shape is not in a container the Shape Data value is "" but if it is in a container, the Shape Data value is read from the User cell in the container. The real magic here is the CONTAINERSHEETREF() function that returns a reference to the container.

Want a great model for all of this? Look at Visio swimlane diagrams. When you drag any flowchart shape into a lane, the Shape Data field called Function automatically displays the text from the header of the swimlane it resides in.

Final comment: if you have to use VBA instead of creating a custom stencil with prebuilt shapes, you can still use the above info. You'll just need to go through a bit of extra work to get the sheet number of the container header and then build the two required formulas, one for the container's User cell and the other for the Shape Data field. Let me know if you need help with that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lucas TarettiAuthor Commented:
Hi Scott,

Thanks for your response.
The shapesheet formula solution fits my need, Im going to use it!

Thanks for the help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Visio

From novice to tech pro — start learning today.