Making a flexible shape, driven by Excel

Started by miless2111s, March 14, 2022, 12:41:40 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

vojo

the "prop.soanso" is specific to this given shape.

So
- if the value in that cell comes from sort of global or page level cell...and that cell changes...then the macro trigger
   for example, if "soandso" is loaded with "5pts star" from some global cell...then ALL shapes would have macro trigger.
- if the value is specific to this shape, then only this shape macro would trigger.
   for example, if "soandso" for shape sheet.123  is changed....but all other shapes unchanged...on sheet.123 macro triggered
- if the value is specific to a group of shapes, then only these shapes' macros trigger
   for example if "soandso" for sheet.123, sheet.456, sheet.789....then only these 3 shapes  will have their macros triggered

So using excel VBA, you could stage which shapes get updated....stars at 11:00    circles at 12:00   squares at 1:00

wapperdude

To minimize confusion, I re-worked the single shape, multiple geometry file to eliminate unnecessary entries.  Plus, added a star shape.
Visio 2019 Pro

miless2111s

#17
Quote from: wapperdude on March 19, 2022, 12:44:24 AM
To minimize confusion, I re-worked the single shape, multiple geometry file to eliminate unnecessary entries.  Plus, added a star shape.
This looks interesting, I am just struggling to understand how it is working :)  More googling required I suspect :)   What are the ,0,1) in the Geometry1.noFill?  I understand the OR(User.shpGeo =1 etc but I am a little confused as to the 0 and 1 :)  This appears on all the Geometry tables...

modified to add:
I think I may have worked it out - is this to make it "False" so that the shape can fill?  If so why would you ever have it set to True, i.e. 1 or is this the mechanism to hide the unwanted Geometry table results?

Further edit - maybe I should go to spec savers? :)  I guess the show / no show is controlled bu the .NoShow setting?

If I have understood this method correctly the approach is that there are any number of Geometry tables in action however the Fill and Show settings control which one is actually visible or can be interacted with?

Surrogate


wapperdude

The Show/NoShow determines which geometry section, i.e., shape type is shown.

The Fill/NoFill is a holdover from original shape.  There were connectors, with arrowheads, as part of the shape.   The arrow heads would also show up in the shape border.  Turns out, the fill/nofill also controls their presence.  When I simplified the shape, I didn't remove the setting nor did I reset the line style to no arrowheads.

One more thing about the fill/nofill, if you have multiple, visible geometry sections, you can get a difference fill effect.

As for learning...make a simple, two geometry section shape.  Then, play with the settings to see how they interact.  Also, in the Protection section, for the circle, square, say I lateral triangle, and star geometries, when they're selected, I protect (lock) the aspect ratio.  Finally, in the width / height cells, I use the SetAtRef() formula construction.  There is a topic in the User Category of the forum that has a mini-tutorial on this functionality: http://visguy.com/vgforum/index.php?topic=6383.msg26747#msg26747

Hope this helps.
Visio 2019 Pro

miless2111s

The multiple geometries idea is working really nicely, thank you.
The next challenge is the connection points :)  Now that I have multiple geometries with different vertices and a different number of points overall is it possible to point to the different geometry sections?

I can use an If statement, such as =if(Prop.row_1 = 1, Geometry1.X1, if(Prop.row_1 = 2, Geometry2.X1,if(Prop.row_1 = 3, Geometry3.X1, if(Prop.row_1 = 4, Geometry4.X1,width*0.5)))) however this could get a bit long if I have as many shape options as I think I will :)

I think I have solved this using the following calculation, which "should" include an error catcher for vertices that don't exist, however the Iferror doesn't appear to be triggered and for these "missing" items it appears to be returning width*0 which is odd.

=IF(ISERROR("Geometry"&Prop.Row_1&".X9"),Width*0.5,"Geometry"&Prop.Row_1&".X9")  - i am having to edit each connection point to X1-10 etc as I can't work out how to add a row() type expression in but it isn't too bad to do this :)

Am I doing something wrong or has Visio got a build-in error handle that returns width*o?


Surrogate

Quote from: miless2111s on March 22, 2022, 08:49:30 AM
The next challenge is the connection points :)  Now that I have multiple geometries with different vertices and a different number of points overall is it possible to point to the different geometry sections?
Do you mean connection points in the vertices of the shape?

miless2111s

Quote from: Surrogate on March 22, 2022, 09:05:35 AM
Quote from: miless2111s on March 22, 2022, 08:49:30 AM
The next challenge is the connection points :)  Now that I have multiple geometries with different vertices and a different number of points overall is it possible to point to the different geometry sections?
Do you mean connection points in the vertices of the shape?
Yes, the definition of the connection points in the "Connection Points" table in the ShapeSheet:
https://imgur.com/MuY1KxL

wapperdude

Unfortunately, Visio has neither the means to add a row nor disable connection points from within  the shapesheet.

One alternative would be to turn on gluing shape vertices.  This would minimize the number connection points needed.  It doesn't help with circles and ovals.   Those would need connection pts if you want specific locations.

Whatever gluing approach you adopt, all "unused" connection points can be "parked" somewhere, say (0,0).  The location must be specifically given. 

An alternative method uses a macro.  The macro could use "case" method; each case being a geometry type.  Then, for each case, 1st delete all rows, then add in just the rows with desired locations.  This would ultimately be cleaner.
Visio 2019 Pro

miless2111s

I frustrating thing has happened a few times and I wonder if it is user error or just normal behaviour...

Whilst setting up various geometry tables for each of the shapes I have found that when I have finished setting up a shape and got it working the entries in the already established geometry tables change!  Recently I had a rectangle, oval, chevron and lozenge shape working and now I have finished working out how to have a start all the other vertices have changed their formula!  This is somewhat frustrating :)

Is there something that I should be doing to avoid this (having them collapsed or something)?

miless2111s

#25
Quote from: wapperdude on March 22, 2022, 03:04:30 PM
Unfortunately, Visio has neither the means to add a row nor disable connection points from within  the shapesheet.

One alternative would be to turn on gluing shape vertices.  This would minimize the number connection points needed.  It doesn't help with circles and ovals.   Those would need connection pts if you want specific locations.

Whatever gluing approach you adopt, all "unused" connection points can be "parked" somewhere, say (0,0).  The location must be specifically given. 

An alternative method uses a macro.  The macro could use "case" method; each case being a geometry type.  Then, for each case, 1st delete all rows, then add in just the rows with desired locations.  This would ultimately be cleaner.
how do I "trap" the situation where the referenced cell doesn't exist?  At the moment the If(iserror(....) doesn't appear to be triggering an error.

Interestingly when looking at my shape and tracking where the connection points are, for the items which don't have matching vertices I have connection points that appear to be "randomly" distributed through the inside of the shape however they are in consistent positions, rather than the width or height *0.5 which would be caused by the "!iserror" triggering.  Could they be remembering something or picking up some false signals?  They're not causing any concerns but I don't like untidy "features" which I don't understand :)

wapperdude

There is only so much that can be done from within the shapesheet.  Here's listing  available functions:  https://docs.microsoft.com/en-us/office/client-developer/visio/iserrna-function.  Potentially, Iserrna were a might be useful.

As your shape has multiple geometries, and given that there's limited shape sheet functionality, it seems the best rout would be to add the max number of connection points and merely park the unused ones.  I don't really believe it's possible to legitimately reference a non-existent row.

Visio 2019 Pro

vojo

if I remember correctly, the connection point row has X Y cells to show hide connection point
for example  0 - 4 = show with various orientations   5 - 9 = hide

I have used this approach to show hid connection points as far back as visio 2003

I have not used it in years, so maybe its another deprecated function (ignored if even presented).

A tedious / partial work around would be something like

Props.connshow = <1/0>
Connections.x1 = guard (if (props.connshow, <some valid geometry>, <some out of the way location>))

Attachment was done around 2005, and it used to show/hide connection points via actions and issnaptarget
it does not seem to work any more

maybe the logic spawns some thinking

wapperdude

Control points have that feature, connection points do not.  I was lamenting that lack of development earlier today.
Visio 2019 Pro

miless2111s

Quote from: vojo on March 22, 2022, 09:02:02 PM
if I remember correctly, the connection point row has X Y cells to show hide connection point
for example  0 - 4 = show with various orientations   5 - 9 = hide

I have used this approach to show hid connection points as far back as visio 2003

I have not used it in years, so maybe its another deprecated function (ignored if even presented).

A tedious / partial work around would be something like

Props.connshow = <1/0>
Connections.x1 = guard (if (props.connshow, <some valid geometry>, <some out of the way location>))

Attachment was done around 2005, and it used to show/hide connection points via actions and issnaptarget
it does not seem to work any more

maybe the logic spawns some thinking
Good grief, when I see something like this shape I realise that I am dabbling in the edges of a deep pond!  Very impressive :)
I am a little confused by the connection point control though, when I display the Connections table I only see one connection however there are many more on the shape - am I looking in the wrong place?