Color-code an org Chart based on Boxes values

Started by glaffitte, March 02, 2016, 05:54:39 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

glaffitte

Hi,

Please help me to solve my issue:

I have Visio 2010 standard and I want, using vba, color all the boxes that have one of the values set as "ITHH". Is there a code that I could use for that?

Thanks a lot!

Surrogate

Quote from: glaffitte on March 02, 2016, 05:54:39 PMIs there a code that I could use for that?
IMHO there haven't this code, someboby will write it especial for you!
Can you attach your example document there?

glaffitte

What I want to do is to color-code the boxes based on the value of the location. For example, I want all the boxes with Bangalore location to be brown.

I have attached an example.

Regards

Surrogate

Sub SomethingLikeAs()
Dim sh As Shape
For Each sh In ActivePage.Shapes
    If sh.CellExists("Prop.Alocation", visExistsAnywhere) Then
        If sh.Cells("Prop.Alocation").FormulaU = """Bangalore""" Then sh.Cells("Fillforegnd").Formula = visDarkRed
    End If
Next
End Sub

glaffitte


glaffitte

Hi again,

I tested today and works perfectly. What should I put instead of ActivePage so the macro applies the color coding to a visio document with multiple pages?

Thanks Again

Surrogate

try this
Sub SomethingElse()
Dim pg As Page
Dim sh As Shape
For Each pg In Activedocument.Pages
For Each sh In pg.Shapes
    If sh.CellExists("Prop.Alocation", visExistsAnywhere) Then
        If sh.Cells("Prop.Alocation").FormulaU = """Bangalore""" Then sh.Cells("Fillforegnd").Formula = visDarkRed
    End If
Next
Next
End Sub

brycentonic

This is exactly what I'm trying to do but the code from Surrogate isn't working for me in Visio Pro 2013. I even downloaded glaffite's original document and it's not running changing anything there. No errors.

wapperdude

Visio 2019 Pro

brycentonic

View - Macros - Macros In - highlight Sub- Run

wapperdude

Open the vba window (alt + f11).  Code s/b in this document, but you might have to hunt for it.  Once you find the code, and it is displayed in the code window, the left mouse click in it, and then use f8 to step thru 1 line at a time.  When you get to if statements, you can watch how each are executed.  If the statements skip making any changes, then perhaps your file doesn't have the necessary info. 
Visio 2019 Pro

brycentonic

Using the Drawing2.vsd document attached above. (So my document wasn't even in play.)
It steps right from this line "If sh.CellExists("Prop.Allocation", visExistsAnywhere) Then" to "End If"

For Each sh In pg.Shapes
    If sh.CellExists("Prop.Alocation", visExistsAnywhere) Then
         If sh.Cells("Prop.Alocation").FormulaU = """Bangalore""" Then sh.Cells("Fillforegnd").Formula = visDarkRed
    End If

Is the If statement coming back a false so the "Then" portion never executes?
Looking at the statement, I don't see valid options for localeSpecificCellName.
CellExists( localeSpecificCellName , fExistsLocally )

https://msdn.microsoft.com/en-us/vba/visio-vba/articles/shape-cells-property-visio

Disclaimer: VERY new at this. Trying to teach myself a bit to solve an ongoing annoyance with Org charts... which would be usable for other diagrams.



wapperdude

Yes, the if statement is returning false, bypassing the then and going to end if.

I haven't looked at the file.  The program is doing the following:  It goes thru the file one page at at time.  For each page, it goes thru each shape on that page.  For each shape, it checks if the prop.alocation exists.  If not, it goes to the next shape.  If prop.alocation does exist, then it checks if it equals Bangalore.  If it does, then it sets the color.

Prop.alocation may not exist in every shape on the page.  When it does exist, its value may not equal Bangalore.  Both conditions are necessary for the color to be assigned dark red.  Note, if the color is already dark red, you won't notice any color change.

When you press f8, the program literally executes one statement at a time.  When there are, say, 10 shapes on the page, the shape loop must execute 10 times, once for each shape.  Similarly, if there's more than one page, the process must repeat for each page.  Once everything has been processed, the code will finally execute end sub statement and be finished.

In the link you provided, the entry "localeSpecificCellName" is merely a syntax place holder.  In use, an actual cell name goes in its place.  In this example, prop.alocation, is the literal name.  Well, technically, prop.alocation.value, but Visio knows that by default, and drops the .value.

HTH,
Wapperdude
Visio 2019 Pro

brycentonic

Possibly a user error problem. I reviewed the logic and since it worked for others, closed and reset the Macro. It worked there.

Taking the logic and trying to mirror that to the project I'm working on... is the next step.

Trying to color the org chart where "Contractors" are one color and anyone working at "XYZ TV" is another color. I'm hitting the same issue here where it's skipping past the "If" statement as if it's not finding anything. 

Sub TestLocal()
Dim pg As Page
Dim sh As Shape
For Each pg In ActiveDocument.Pages
For Each sh In pg.Shapes
    If sh.CellExists("Prop.Alocation", visExistsAnywhere) Then
         If sh.Cells("Prop.Alocation").FormulaU = """Contractor""" Then sh.Cells("Fillforegnd").Formula = visDarkRed
    End If
   
    If sh.CellExists("Prop.Alocation", visExistsAnywhere) Then
         If sh.Cells("Prop.Alocation").FormulaU = """XYZ TV""" Then sh.Cells("Fillforegnd").Formula = visDarkRed
    End If

Next
Next
End Sub

wapperdude

Your shapes have a shape data entry Prop.Alocation?

You'll need to save as vsd for me to look at the file.

Wapperdude
Visio 2019 Pro