Hi ALL,
I need your help (code) to do the following:
I have a diagram with 5 columns, each may have 5 - 10 unconnected Process shapes - focusing on one column, I need to move a shape say in the 6th positon to 3rd position. doing so, the shapes that were in the 3,4,5 would move downward giving room to the selected shape to be placed in the 3rd position. Likewise, moving the 2nd position shape to the 5th, shapes 5,4,3 would move upwards.
My thought to do this, once a shape is selected to move, its position is logged and as its current position in the motion changes, if upward motion detected, the closest shape in the column would move downward, and so on. Please help, Thanks!
IMHO...way too complicated...better off doing the rearrangement by hand
(<30 seconds each time manually vs a month getting code to work and then support it)
(in essence, you want recreate shape dragging and add text).
Could certainly do it in code, but so many combinations
(shape 1 to 5th position, 2 to 3, 4 to 2, etc...then same for col 1,2,3,4,5).
Might be better off using an excel workbook
- 5 columns for 5 columns
- row 1 = shape type
- row 2 = text in shape
code would clear drawing, pull from excel, and add shapes, populate, and align.
For changes, update workbook and rerun code. That would be more manageable
I think you will find that this will be a lot of work for limited return
https://www.google.de/?gws_rd=ssl#newwindow=1&safe=off&q=visio+containers+lists
Thank you for your reply - it is very important to do it in Visio (leadership request).
I think if you review my suggestion, we can divide and conquer any difficulties.
1. I need a code that; focusing on one column via X value, would check the selected shape-A, Y value as I move it.
2. If the Y value is > the shape-B above it, Shape-B would take its place (I know the X,Y of shape-A).
3. Reverse the logic when moving the other direction.
4. set the code in a loop until shape-A is deselected.
Is that doable, Thanks!
Yes, do-able. But not easy...needs events.
The basic problem is that none of the shapes know anything about each other. Their "isolation" is even stronger because they are not connected. I think my approach would be to loop thru all the shapes on the page and store them in a multi-dimensional array. That way, they can be organised by column (x-pos), and then by y-pos. Then, based upon selected shape x-, y-position you can determine the column and all shapes in it, and also who the neighbors are. That's all pretty easy.
The tough part, as I see it,is moving the shape to a new location. There must be an event(s) to trigger this, and it must be a unique event that is associated with the shape in question. Perhaps mouse down / up events are adequate??? A variety of decisions must be made:
1st event: shape vacates existing spot. Is the array updated, filling in the vacated spot by moving all shapes below upward, or some other code-able scheme?
2nd event: shape placed in new location. Let's say mouse up signals final resting spot (for the newly departed ::) ). Now add shape back into the array, moving down shapes that will be below it. Might require some temporary "bookkeeping" arrays for keeping track of things.
Something to think about as a possible methodology.
HTH
Wapperdude
Fleshing out a possible approach...
1) User starts code to re-arrange shapes
a) loop thru all shapes on page and enter into an array: index A = shape Name, index B = xpos, index C = ypos
b) resort array to neatly arrange shapes by x & y values
2) Code asks user to select shape to be moved
a) mouse down event starts desired sub routine to extract selected shape and fill it's vacancy.
b) drag shape to desired new location
c) mouse up event starts final shape re-arranging. Note, there may be some timing issues. This code action must wait for previous code action to complete.
I'm sure there are other ways to approach this, and there's no guarantee that all of the suggested steps can be performed as outlined.
Wapperdude
Thank you for your reply,
Great approach - I agree one needs to use Events, as stated, the imitation/trigger is the selection of a shape and moving it. Using an array is good, ,but I thought one can build a code that focus only on one column by logging the selected shape's X value then build the array for all shapes with that value.
I am a novice in this area and ask for your help coding this loop, Thanks!
Yes, you could force the code to do a single column...for example, use "IF" statement to find shapes that match X- value of selected shape. Perfectly fine approach. Probably easier to implement since there's fewer things to track.
The loop would go thru all shapes on the page, compare PinX value to PinX of selected shape. If successful, put into array.
Wapperdude
I understand, but the code is what I need help with ;-)
Good way to start is to download the SDK for your version of Visio. Contains much info and examples. Also, the macro recorder is very useful for specific commands. Of course it won't give you things like loops, if statements. Also, Google something like Visio vba some task, here some task might be find shapes on a page, or for next loop, or get shape position on a page.
Wapperdude
Since sorting the array is not built into VBA, it would take some code development. I found the following code. As provided,there are two pieces: a sub, and a function. The sub would be replaced by your code development. But your code needs to include the function call.
In the example sub, a 1-dimensional array was hard coded. You will have 2-dimensional array (shape Name, yLoc). The function needs to be modified for 2-dimensional array. Sorting will be based upon yLoc values.
Wapperdude
Option Explicit
Sub SortArray()
' hiker95, 03/11/2013
' http://www.mrexcel.com/forum/excel-questions/690718-visual-basic-applications-sort-array-numbers.html
Dim MyArray As Variant, i As Long
MyArray = Array(1, 4, 2, 32, 5, 21)
' True being sort as Ascending. False will sort Decending.
MyArray = BubbleSrt(MyArray, True)
For i = LBound(MyArray) To UBound(MyArray)
MsgBox MyArray(i)
Next i
End Sub
Public Function BubbleSrt(ArrayIn, Ascending As Boolean)
' rjwebgraphix, 03/11/2013
' http://www.mrexcel.com/forum/excel-questions/690718-visual-basic-applications-sort-array-numbers.html
' True being sort as Ascending. False will sort Decending.
Dim SrtTemp As Variant
Dim i As Long
Dim j As Long
If Ascending = True Then
For i = LBound(ArrayIn) To UBound(ArrayIn)
For j = i + 1 To UBound(ArrayIn)
If ArrayIn(i) > ArrayIn(j) Then
SrtTemp = ArrayIn(j)
ArrayIn(j) = ArrayIn(i)
ArrayIn(i) = SrtTemp
End If
Next j
Next i
Else
For i = LBound(ArrayIn) To UBound(ArrayIn)
For j = i + 1 To UBound(ArrayIn)
If ArrayIn(i) < ArrayIn(j) Then
SrtTemp = ArrayIn(j)
ArrayIn(j) = ArrayIn(i)
ArrayIn(i) = SrtTemp
End If
Next j
Next i
End If
BubbleSrt = ArrayIn
End Function
Will you be the only one doing this?
There is a pretty simple method that doesn't require coding...well, it could be coded. The core to the solution is the distribution shapes function. Evenly distributes shapes between 1st and last shapes. Optimum for uniformity: 1st and last shapes placed in their final locations vertically.
Methodology. Select and move shape to desired approximate location vertically...somewhere between two shapes. Location does not have to be precise. Next, select all shapes in the column. Finally use the distribution function to evenly space the interior shapes between the 1st and last shapes. That's it. Quick and simple.
Wapperdude
OK. This was pretty quick to code up.
Sub Macro1()
Dim vShp As Shape
Dim pShp As Shape
Dim Xmin As Double, Xmax As Double 'Optional: sets horiz limits for shapes to be in same column
'Select a shape to be moved and place it...note, should add check to verify a shape has been selected.
'or use event to fire macro, in which case the shape is selected and no further check ended.
Set vShp = ActiveWindow.Selection(1)
Xmin = vShp.CellsU("PinX").Result("in") - 1 'Limits are tentatively based upon selected shape position. Not optimal.
Xmax = vShp.CellsU("PinX").Result("in") + 1
ActiveWindow.DeselectAll
'Select shapes in the column:
For Each pShp In ActivePage.Shapes
If pShp.CellsU("PinX").Result("in") > Xmin And pShp.CellsU("PinX").Result("in") < Xmax Then
ActiveWindow.Select pShp, visSelect
End If
Next
ActiveWindow.Selection.Distribute visDistVertMiddle, False
End Sub
See attached for example. I colored the shapes of interest in yellow. Select one of these shapes. In the example, Process.6 was selected and moved. Just re-select, then run the macro.
For details about "distribute": https://msdn.microsoft.com/en-us/library/office/ff766983.aspx (https://msdn.microsoft.com/en-us/library/office/ff766983.aspx)
HTH
Wapperdude
Hi Wapperdude,
Waw... your input and help is greatly appreciated and I will try your last suggestion and post my results... Thanks again for all your help :-) :)
Hi Wapperdude,
The last code to distribute worked but because I have other shapes within the x axis (i.e. diagram block, Legends...) I decided to move all my process shapes in a separate layer.
I need to ask you 2 things:
1. How to force the code to run in a specific layer
2. How to resort the newly distributed shapes IDs correspond to the new location - meaning: when I move shape ID-3 top position 5 it will reorder to ID-5 and ID-4 will be 3 and ID-5 will be 4.
Thanks!
Regarding item 1):
In the example, add a layer to the yellow shapes, name it, uh, "Yellow". ??? Then, make this new layer
the only assigned layer for the yellow shapes. Put some non-yellow shapes in the yellow shape column. Run the macro as usual.
Caution: this is simple code. Assumes all shapes are assigned to 1 layer, whatever it may be. The code could be made more robust to:
1) check for no assigned layers
2) check for multiple assigned layers
Note1: could assign unique layers to every shape, say, "LayC1", "LayC2", to establish
column identity and valid, sortable shapes within the column.
Sub Macro1()
'Uses layer property to distinguish valid shapes in a column.
'Note: could assign unique layers to every shape, say, "LayC1", "LayC2", to establish
'column identity and valid sortable shapes within the column.
Dim vShp As Shape
Dim pShp As Shape
Dim Xmin As Double, Xmax As Double 'Optional: sets horiz limits for shapes to be in same column
Dim vLay As Layer
'Select shape to be moved and place it
Set vShp = ActiveWindow.Selection(1)
Xmin = vShp.CellsU("PinX").Result("in") - 1 'Limits are tentatively based upon selected shape position. Not optimal.
Xmax = vShp.CellsU("PinX").Result("in") + 1
If vShp.LayerCount = 0 Then 'If no layer assigned to shape, exit sub.
MsgBox ("No layer assigned. Assign layer to shape and retry.")
Else
Set vLay = vShp.Layer(1) 'get layer name of shape
ActiveWindow.DeselectAll
'Select shapes in the column:
For Each pShp In ActivePage.Shapes
If pShp.Layer(1) = vLay Then
If pShp.CellsU("PinX").Result("in") > Xmin And pShp.CellsU("PinX").Result("in") < Xmax Then
ActiveWindow.Select pShp, visSelect
End If
End If
Next
Application.ActiveWindow.Selection.Distribute visDistVertMiddle, False
End If
End Sub
Not sure I understand item 2). Are you saying that in the attached picture, this is not what you want?
Wapperdude
Using "layers" as a way of identifying shapes in a column, that is, each shape has a layer that identifies the column it is in, then the code becomes really simple. Select the shape to be re-arranged, get the layer assignment (only 1 allowed), then just search the page for all shapes with same assignment.
Sub Macro1()
'Uses layer property to distinguish valid shapes in a column.
Dim vShp As Shape
Dim pShp As Shape
Dim vLay As Layer
'Select shape to be moved and place it
Set vShp = ActiveWindow.Selection(1)
If vShp.LayerCount = 0 Then 'If no layer assigned to shape, exit sub.
MsgBox ("No layer assigned. Assign layer to shape and retry.")
Else
Set vLay = vShp.Layer(1) 'get layer name of shape
ActiveWindow.DeselectAll
'Select shapes in the column:
For Each pShp In ActivePage.Shapes
If pShp.LayerCount = 0 Then GoTo Continue 'Special case: no layers assigned
If pShp.LayerCount = 1 And pShp.Layer(1) = vLay Then 'Error check, must have only 1 layer assigned
ActiveWindow.Select pShp, visSelect 'Select shapes in column
End If
Continue:
Next
ActiveWindow.Selection.Distribute visDistVertMiddle, False
End If
End Sub
Wapperdude
OK - to clear my point please reference to attach image - there you will find the controls and their order; they are linked to data as you can see on the right.
So, what I was asking in #2; is how after I move a control say 5 to the 3rd position, its ID would also change to "3" and the other controls would change accordingly - I know it is a lot to ask, but you can understand that, when done, it would save a lot of time during a leadership meeting.
That does complicate things. It forces position tracking of each shape--not part of the distribution approach.
You don't want to be changing shape names. That means the text displayed needs to be a custom property. You could make a index list, that keys off the y-location and displays the desired text. Or, you could do the array thing and use the array position to determine the desired text.
I assume there's other data associated with each shape, hence their re-positioning. How is the data assigned? Couldn't you just re-assign the data to the correct shape location?
Index list:
http://visguy.com/vgforum/index.php?topic=5902.0 (http://visguy.com/vgforum/index.php?topic=5902.0)
https://msdn.microsoft.com/en-us/library/office/ff768335.aspx (https://msdn.microsoft.com/en-us/library/office/ff768335.aspx)
Wapperdude
Thanks - I will give that a try.
1Q: Can you help me with a code that, on mouse release (left button) your code will run? so as I move a shape to its new location and release the mouse, the code would run to distribute the shapes.
Thanks!
and to answer your Q "I assume there's other data associated with each shape, hence their re-positioning. How is the data assigned? Couldn't you just re-assign the data to the correct shape location?", is YES, I can do that, I only thought you may came across that before.
You know - one would think Microsoft Visio developers would have incorporated these features to the product; as you can see, what I am asking fore is a real time saver and add value to Visio.
Not sure how much time I can spend on this. I have other things to attend to. Perhaps other contributors will add to this. Also, take a try at it. Get stuck, share what you've got. Everyone is volunteer here, most will help with suggestions, but don't have the time to take on a full-blown coding project.
Wapperdude
Well, I thank you for all your help and I will share my findings once I get all the pieces in place.
Thank You!
Since Wapperdude gave up ( ;) ), I'll take over again and remind Abe about my first post in this topic.
It was about Lists and Containers. This is a feature available since V2010 and brings a neat function with it, namely LISTORDER() and it does exactly what its name promises: it returns the list order of the list item. There's not much to say more. The options for tweaking lists are numerous, but there are some very nice articles about them in the net (Google is your friend).
Hope you can get along with these hints.
Rgds,
Y.
Hi Vacine,
Thanks for stepping in, but I also like to thank Wapperdude for his help.
As you may have noticed, I like data to Visio document and generate the diagram above automatically - this is done via code. then during the Leadership meeting, we move many of these processes to fit the new requirements - Therefore, if you think I can generate the same diagram with container for each column (there are 5-7 columns), then please let me know the instructions needed to embed.
To my understanding; if I move a process from one position and place it in-between two other processes in the same container, they will give way to the newly positioned process, align, and reorder; is that correct?
You're welcome Abeiis.
Yacine was kidding around. He knows I don't have newer Visio versions, so cannot contribute to container related issues.
The container list will help, but I think you will still need index lookup, based on container result in order to update the shape text. There also remains the issue of automatically triggering the macro.
Neither containers nor events are issues I can readily address.
Wapperdude
Regarding "also remains the issue of automatically triggering the macro.", I researched my options and found on MSDN one solution talking about "Application.MouseMove Event (Visio)".
https://msdn.microsoft.com/en-us/library/office/ff766075.aspx
I tried it, but it only works at opening the drawing file - I thought it would work as with Access Mouse Events... any thoughts is appreciated.
I also researched the List Control (see link) and Scott talked about the control text and how the control changed its order number but did not elaborate on how it was done; if you can shed some light on how he did it, I would appreciate it, Thanks!
https://www.youtube.com/watch?v=UNhxdSRGAmc
Hello Abe,
I also don't know lists very well. So I started the macro recorder and did some basic operations (adding a shape, moving a list item to another position and so on).
That's what I got:
1) adding a shape to a list:
dim newShp as shape
dim listShp as shape
PositionInList as integer
set newShape = Application.Documents.Item("Path to your stencil...\Lists.vsdx").Masters.ItemU("Master.6") 'Shape to drop
set listShp = ... find a strategy to identify the target list...
Application.ActivePage.DropIntoList , newShp, listShp, PositionInList
2) link this shape to a data row
RS = 1 'Recordset ID - in my case =1. Please check how to get the ID for a generic case.
RowNum - the row number - iterate over all the rows to draw your graph.
newShp.LinkToData RS, RowNum, False
3) Reorder the list items by means of a data field (eg mySort)
listShp.ContainerProperties.ReorderListMember newShp, mySort
HTH,
Y.
Adding to Yacine, thus, will assume you now have a listcontainer with shapes in it. First, a disclaimer: I have no way of testing the following to know if true and works.
The code from above could still be used to move the shapes within the container. It would no longer be necessary to use layers as you could use the parent listcontainer instead. Saves adding a layer to each shape. The code would need modification, not shown here, whether you use layers or not, since I believe the contained shapes are treated as subshapes. Thus, it might be necessary to push into the container first. But, layers would still work once inside the container. Also, I'm not sure is if the re-distribution which changes the position in the listcontainer automatically updates the listorder. I think it should.
Then, after all the sorting has finished, below is proposed code to update the displayed shape identifier. Note, the actual shape name has not changed, only this pseudo-identification. As previously mentioned, you don't really want to muck around with shape name changes.
****************************************
*
* Still need event code development to automatically fire the process.
* Easy to use double click event in the shapesheet and use Runmacro function.
* Not automatic, but much easier and quicker. Does require shapesheet editing for each shape.
*
****************************************
'
'Add code:
'Iterate to find new position of each shape in the list container
'Update the displayed text. Note, actual shape name will not necessary match what's displayed
'
' Dim vChars1 As Characters
' Dim MyPos as integer
'
' For Each pShp In ActivePage.Shapes
' If pShp.LayerCount = 0 Then GoTo Continue 'Special case: no layers assigned
' If pShp.LayerCount = 1 And pShp.Layer(1) = vLay Then 'Error check, must have only 1 layer assigned
' MyPos = pShp.GetListMemberPosition(ShapeMember)
' Set vChars1 = pShp.Characters
' vChars1.Begin = 0
' vChars1.End = 1
' vChars1.AddCustomFieldU """Process"" & MyPos", visFmtNumGenNoUnits 'This inserts the displayed text for the shape, e.g., Process3
' End If
' Next
And the entire code module becomes:
Sub Macro1()
'Uses layer property to distinguish valid shapes in a column.
'******
'NOTE: could use container as the property for shapes in column, but, note avail in V2007
'Add pseudo code to get container position...requires iterating thru shapes after re-distribution
'
Dim vShp As Shape
Dim pShp As Shape
Dim vLay As Layer
Dim vSel1 As Selection
'Select shape to be moved and place it
Set vShp = ActiveWindow.Selection(1)
If vShp.LayerCount = 0 Then 'If no layer assigned to shape, exit sub.
MsgBox ("No layer assigned. Assign layer to shape and retry.")
Else
Set vLay = vShp.Layer(1) 'get layer name of shape
ActiveWindow.DeselectAll
Set vSel1 = ActiveWindow.Selection
'Select shapes in the column:
For Each pShp In ActivePage.Shapes
If pShp.LayerCount = 0 Then GoTo Continue 'Special case: no layers assigned
If pShp.LayerCount = 1 And pShp.Layer(1) = vLay Then 'Error check, must have only 1 layer assigned
vSel1.Select pShp, visSelect 'Selected shapes are not highlighted
End If
Continue:
Next
vSel1.Distribute visDistVertMiddle, False 'True adds guides, good for reposition begin/end shapes.
'
'Add pseudo code:
'Iterate to find new position of each shape in the list container
'Update the displayed text. Note, actual shape name will not necessary match what's displayed
'
' Dim vChars1 As Characters
' For Each pShp In ActivePage.Shapes
' If pShp.LayerCount = 0 Then GoTo Continue 'Special case: no layers assigned
' If pShp.LayerCount = 1 And pShp.Layer(1) = vLay Then 'Error check, must have only 1 layer assigned
' MyPos = pShp.GetListMemberPosition(ShapeMember)
' Set vChars1 = pShp.Characters
' vChars1.Begin = 0
' vChars1.End = 1
' vChars1.AddCustomFieldU """Process"" & MyPos", visFmtNumGenNoUnits 'This inserts the displayed text for the shape, e.g., Process3
' End If
' Next
End If
End Sub
Wapperdude
I should also emphasize that Yacine's original response has multitude of links regarding containers and their behavior. You really need to explore those.
Wapperdude
Hello Wayne,
I'm "almost" with you about the next steps, but not completely.
When Abe speaks about an ID, he must be meaning a sort field - no real shape ID, nor shape name or so.
The initial value of the sort field is used for the initial drawing, but it does not update when the list items are re-ordered. There's only a code link from the sort field to the actual order.
My suggestion - a user formula: setf(getref(prop.sort), listorder()) + dependson(listorder()). This way no event handling is required. No macro at all. That is, Abe will certainly ask to get the new order back to his database. That's where some tedious database coding is necessary: define a dao/ado recordset and link it to external source, loop through the datarecordset and write everything to the external source.
@Abe, having read your posts, I wonder if you have the skills to implement such a solution. Sorry for being so rude, but you seem to expect the forum to work out the final solution for you. Would it make sense to hire a programmer?
Thanks - I will pursue your directions and update our discussion.
I always say; divide and conquer; in doing so, one learns and share its findings.
Here is what I do via code:
1. I created a Visio Template with VBA to connect a DB table to Visio
2. I already have a sorting field from the linked Db [OrderOfPriority], see diagram (Page-2).
3. I take each section (i.e. i, ii, iii, iv... etc.) and place a shape with a preset x/y/ coordinate on the active page. (and that is what I asked Yacine if it could be done in a List Control.)
4. Then I link the data in the same order to the shapes.
The output diagram is what I work on with the leadership team to move shapes and finalize the process, and that is what I was asking here to automate.
Wapperdude is correct in stating I need to update the DB with the new order after my Leadership meeting; and yes, I could do this with an ODBC connection but Microsoft changed their approach with ODBC and how it's functions between Visio and Access.
Q: What did you think of Scott's tutorial video? at 2.35 min. he show the added list controls take the numeric ID, and I am wondering how it is done - https://www.youtube.com/watch?v=UNhxdSRGAmc
I did leave a Q for him to answer, it does not seam too cumbersome; but if I could place the shapes in a List for each of column using the [OrderOfPriority] field value, and having the order value change as I manfully move it from position to another in the same List, then I'm done.
Regards, Abe
Quote from: Abeiis on May 03, 2017, 06:20:36 PM
3. I take each section (i.e. i, ii, iii, iv... etc.) and place a shape with a preset x/y/ coordinate on the active page. (and that is what I asked Yacine if it could be done in a List Control.)
Sure, that is the function DropIntoList that I mentioned before. To be used with the reorder function to get the right sorting.
QuoteQ: What did you think of Scott's tutorial video? at 2.35 min. he show the added list controls take the numeric ID, and I am wondering how it is done - https://www.youtube.com/watch?v=UNhxdSRGAmc (https://www.youtube.com/watch?v=UNhxdSRGAmc)
I did leave a Q for him to answer, it does not seam too cumbersome; but if I could place the shapes in a List for each of column using the [OrderOfPriority] field value, and having the order value change as I manfully move it from position to another in the same List, then I'm done.
That's what the file uploaded by myself shows. Download my attachment and play with it to understand the list behaviour. Have a look at the shapesheet.
Thanks Yacine - I did use your code in the document to rearrange the shapes and I also added a line of code to align Left in-case I dropped the shape off-alignment - but all that is omitted if we use the List Box you have suggested before, and I agree the List Box does what I need except I am not yet able to change the ID (or any other field that one can preset) as with the video by Scott indicated (see link in my previous post).
Hi ALL,
I found how Scott got the number in the List Control, and it is simple.
1. Select the List Control
2. In the Tools Ribbon - Insert Tab - select [Field]
3. Select Custom Formula
4. Then type "=LISTORDER()"
This is what I was looking for - I just need to find a way to point my linked field [OrderOfPriority] to the custom formula.
Regards,
Abe
??? Have a look at the attachment in Reply #23 (the vsdx)
I did, and I did not see any code - I was hoping to see working example to place a control from the document stencil on to a List Control with a loop to add 3 controls in each of the List Boxes that will be greatly appreciated - you can use the attached template, Thanks!
PS: How do I rename a listbox - when I place the 1st ListBox it takes its name from master (stencil), and as I add more Listboxes, it skips 4 number and added it to the name. Looking forward; as we add ListItems, I want to be able to cycle through the Listboxs via variable "i" and therefore I need to rename these Lists incrementally.
Regards,
Abe
Dear ALL,
It's a good feeling to conquer multiple obstacles ;-)
I reached the solution to my original question (see attachment), and I hope this will help others in this forum.
Notice - See code in the Document VBA Editor - 3 ListBoxes need to be placed already in the diagram.
My next task is, to find a way to link data from an Excel or Access to each of the List-Controls... food for thoughts.
Regards,
Abe