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
    • ShapeSheet™ Knowledge Base
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 Integer
For i = 1 To ActiveWindow.Selection.Count
    Set shp = ActiveWindow.Selection(i)

Next
End 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
    • ShapeSheet™ Knowledge Base
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.Master
Dim shpsObj As Visio.Shapes, shpObj As Visio.Shape
Dim StnObj As Visio.Document
Dim PathName As String
Dim FullFileName As String
Dim curShapeIndx As Integer
FullFileName = 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.Close
Next curShapeIndx
StnObj.Save
StnObj.Close
Set StnObj = Nothing
End 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
    • ShapeSheet™ Knowledge Base
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.

I'm not a telepath and cant read your minds. Sorry…
« 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