### Author Topic: Adding formula to same cells of multiple shapes?  (Read 779 times)

0 Members and 1 Guest are viewing this topic.

#### freshlychurnedbutter

• Jr. Member
• Posts: 26
##### Adding formula to same cells of multiple shapes?
« on: February 06, 2022, 10:07:42 AM »
Is it possible to select multiple shapes and add a formula to the geometry.noshow and hidetext cells? Currently I have to get into each shapesheet and enter the formula. Gets kinda tedious when I need to enter the formula for 15 shapes...

#### Yacine

• Hero Member
• Posts: 2950
##### Re: Adding formula to same cells of multiple shapes?
« Reply #1 on: February 06, 2022, 10:51:23 AM »
Well that is actually the right way to do it.
You just may want to add a pinch of VBA to it.

sub doStuff
dim shp as shape

for each shp in activewindow.selection 'that's the point
shp.Cells("Geometry1.noshow").formula = 1 ' or 0
next shp

end sub
Yacine

#### freshlychurnedbutter

• Jr. Member
• Posts: 26
##### Re: Adding formula to same cells of multiple shapes?
« Reply #2 on: February 06, 2022, 11:06:25 AM »
That's what I kinda gathered. Now my VBA skills are fairly weak. I hope you can help me address a few questions.

1. How can I index through a specific selection of shapes, rather than all the shapes on the drawing?
2. Rather than setting the cell to 0 or 1, can I insert a formula into the cell instead?

#### Surrogate

• Hero Member
• Posts: 1566
##### Re: Adding formula to same cells of multiple shapes?
« Reply #3 on: February 06, 2022, 02:36:04 PM »
1. How can I index through a specific selection of shapes, rather than all the shapes on the drawing?
Code
``Sub bb()Dim shp As Shape, i As IntegerFor i = 1 To ActiveWindow.Selection.Count    Set shp = ActiveWindow.Selection(i)…NextEnd Sub``
2. Rather than setting the cell to 0 or 1, can I insert a formula into the cell instead?
Code
``shp.Cells("Geometry1.noshow").FormulaU = "IF(Width>10 mm,1,0)"``

#### freshlychurnedbutter

• Jr. Member
• Posts: 26
##### Re: Adding formula to same cells of multiple shapes?
« Reply #4 on: February 06, 2022, 09:39:11 PM »
Thanks guys! I'll give this a shot and let you know how it turns out! Might save me from having to manually enter a bunch of formulas.

#### freshlychurnedbutter

• Jr. Member
• Posts: 26
##### Re: Adding formula to same cells of multiple shapes?
« Reply #5 on: February 07, 2022, 01:14:24 PM »
How can I modify this code to work with masters? I am currently editing a master and it seems like this only works for shapes while in a drawing.

#### Surrogate

• Hero Member
• Posts: 1566
##### Re: Adding formula to same cells of multiple shapes?
« Reply #6 on: February 07, 2022, 02:29:49 PM »
You can modify another code
Quote from: John Marshall in article VBA
Edit Stencil: This code segment shows how to edit the shapes in a stencil.
Code
``Dim mstObj As Visio.Master, mstObjCopy As Visio.MasterDim shpsObj As Visio.Shapes, shpObj As Visio.ShapeDim StnObj As Visio.DocumentDim PathName As StringDim FullFileName As StringDim curShapeIndx As IntegerFullFileName = PathName & "test1.vss"Set StnObj = Documents.Open(FullFileName)For curShapeIndx = 1 To StnObj.Masters.Count   Set mstObj = StnObj.Masters(curShapeIndx)   Set mstObjCopy = mstObj.Open   Set shpsObj = mstObjCopy.Shapes   Set shpObj = shpsObj(1)   ' do something to the shape.     mstObjCopy.CloseNext curShapeIndxStnObj.SaveStnObj.CloseSet StnObj = NothingEnd Sub``
« Last Edit: February 07, 2022, 02:35:21 PM by Surrogate »

#### freshlychurnedbutter

• Jr. Member
• Posts: 26
##### Re: Adding formula to same cells of multiple shapes?
« Reply #7 on: February 07, 2022, 03:08:36 PM »
Sorry I'm very new with VBA. When I try to run the code, I get an error message that the file name is not valid. Now I'm know that it's probably because I don't have a file called "test1.vss" for it to open. More than that though, I'm confused what the script is actually doing. Is this code assuming that I don't have the master opened and it's trying to open it for me? Currently I have the edit master shape window open and within that window, I have several shapes. For a few of the shapes, I need to edit the formula for User.TagID.Value cell. Again sorry if it seems like I'm asking for others to do my work but I've been spending the past week opening up the shapesheet of each individual shape and pasting formulas into the proper cells. I haven't been able to write any coherent code in VBA so far.

#### Surrogate

• Hero Member
• Posts: 1566
##### Re: Adding formula to same cells of multiple shapes?
« Reply #8 on: February 07, 2022, 03:51:15 PM »
When I try to run the code, I get an error message that the file name is not valid. Now I'm know that it's probably because I don't have a file called "test1.vss" for it to open. More than that though, I'm confused what the script is actually doing.
No doubt this code which was written about 20 years ago can't do what you need ! I refer this code as example, it explain how open stencil and iterate master shapes into stencil.

« Last Edit: February 07, 2022, 03:54:02 PM by Surrogate »

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 4367
• Ideas Visio-lized into solutions
##### Re: Adding formula to same cells of multiple shapes?
« Reply #9 on: February 07, 2022, 04:41:42 PM »
Hmmm.  Perhaps it might be good to review couple things to make sure everyone is on same page.  It seems like that's not quite the case here.  This may be obvious to most, if not everyone, but felt compelled to shared.  So apologies if this is redundant info.

1) when a shape (master) is dragged from a ref stencil and dropped onto a drawing page, Visio creates a copy of master in the Doc stencil.  This becomes the local reference for the Visio file.

2) editing the master in the ref stencil does not impact the drawing.  Dragging the changed master and dropping will create a new instance and new version in Doc stencil.  Previously placed objects unaffected.

3) changing a master in Doc stencil will update all shapes in file that reference THAT master.

Code can be designed to change shapes on a page, shapes in Doc stencil, and/or the ref stencil.  Also, code can be designed to look at all shapes on a page and/or all pages, or just selected shapes, or shapes with certain, specific characteristics.

Yacine's pseudo-code (not actual code) searches selected shapes on a single (the currently active) page.  It presumes you've selected the shapes to be changed.

Next, Surrogate provides actual code that loops thru the selected shapes on a single page.  These suggestions have restrictions to meet request to look at selected shapes.

Surrogate's next sample code looks at some stencil...you need to supply the specifics... and then iterates thru all masters on that stencil.  This will not impact the drawing.

There are a few points here...
1) if editing manually, there would be some benefit to edit masters in Doc stencil
2) edit ref stencil masters is a global thing that would impact all future users.
3) ref stencil changes only impact subsequent drag n drops.
4) it is perfectly normal to edit shapes and masters currently placed.
5) to edit only certain, specific shapes you can manually select them, edit their Doc master, or loop thru all and use a "filter" to sort thru which shapes should get edited.

« Last Edit: February 07, 2022, 04:45:58 PM by wapperdude »
Visio 2019 Pro

#### Yacine

• Hero Member
• Posts: 2950
##### Re: Adding formula to same cells of multiple shapes?
« Reply #10 on: February 08, 2022, 01:42:59 AM »
Not sure if this applies here, but I had an idea that might being worth investigating.

Setting: you have one or several documents with outdated shapes. So much shapes that automation is required.

1. Edit the master or create a new one in the STENCIL
2. Iterate over the document, then the pages, then the shapes and replace the shapes by the new master via VBA function "ReplaceShape". (https://docs.microsoft.com/en-us/office/vba/api/Visio.shape.replaceshape)
3. If wanted you may reset particular properties of the shapes by setting their values to "" (obviously only if they existed already.
Yacine