Integrate Formula Value in ShapeSheet

[Simone]

Hello,

Really appreciate any help.
In the attachment called ShapeSheetData.PNG  Shows the shapesheet and shape data.  In the shapesheet for Prop.projectType the Value column is a formula  "INDEX(0,Prop.projectType.Format)"  This is because the property itself is a drop down list item. 

I have a current macro I have added a nested If statement to integrate the drop down values to change the LineColor of the shapes based on the selection choosen.   The integration with the following code it doesn't recognise the formula as the valid value for the property.  Why is this and is there a better method?

If I remove the nested if statement everything else works for the macro.  The full macro has more ElseIF statements for the first IF statement, but I felt that was unneccessary to have to give enough information.

I am not sure if I should be using a different method or order things differently.  But logically I would think to replace shape and then change the other properties after that.   

I am not sure if this should be approached by another method of setting the LineColor as formula that is basing it's color on these prop values.  I did read this article but couldn't work it out if this is possible for what I am trying to acheive - http://www.visguy.com/2010/07/10/nesting-visio-shapesheet-if-functions/

Code Select Expand


Sub ChangeLineColour()
Dim sh As Shape
Dim vsoShapes As Visio.Shapes
Dim vsoShape As Visio.Shape
Set vsoShapes = ActivePage.Shapes

For Each vsoShape In vsoShapes
   
   
    If vsoShape.CellsSRC(visSectionProp, 4, visCustPropsValue).Formula = Chr(34) & "Goal" & Chr(34) Then
        Set sh = vsoShape.ReplaceShape(Application.Documents.Item("scdemo339-443.vssx").Masters.ItemU("Circle"))
        Dim row As Visio.row
        Set row = sh.Section(visSectionObject).row(visRowFill)
        Debug.Print sh.Name
        row(Visio.VisCellIndices.visFillForegnd).FormulaForceU = "THEMEGUARD(RGB(38,87,153))"
        row(Visio.VisCellIndices.visFillBkgnd).FormulaForceU = "THEMEGUARD(SHADE(FillForegnd,LUMDIFF(THEMEVAL(""FillColor""),THEMEVAL(""FillColor2""))))"
        row(Visio.VisCellIndices.visFillGradientEnabled).FormulaForceU = "FALSE"
        sh.Cells("Width").ResultForce(Visio.VisUnitCodes.visMillimeters) = 35
        sh.Cells("Height").ResultForce(Visio.VisUnitCodes.visMillimeters) = 35
           
            '//Nested if statement integrating a value that is set as formula is not working
           
            If vsoShape.CellsSRC(visSectionProp, 6, visCustPropsValue).Formula = Chr(34) & "INDEX(0, Prop.ProjectType.Format)" & Chr(34) Then
              sh.CellsSRC(visSectionObject, visRowLine, visLineColor).FormulaU = "THEMEGUARD(RGB(0,176,80))"
   
            ElseIf vsoShape.CellsSRC(visSectionProp, 6, visCustPropsValue).Formula = Chr(34) & "INDEX(1, Prop.ProjectType.Format)" & Chr(34) Then
            sh.CellsSRC(visSectionObject, visRowLine, visLineColor).FormulaU = "THEMEGUARD(RGB(0,112,192))"

            ElseIf vsoShape.CellsSRC(visSectionProp, 6, visCustPropsValue).Formula = Chr(34) & "INDEX(2, Prop.ProjectType.Format)" & Chr(34) Then
            sh.CellsSRC(visSectionObject, visRowLine, visLineColor).FormulaU = "THEMEGUARD(RGB(0,112,192))"

            Else
             End If
             
      Else
      End If
       
       
Next
MsgBox "Script is complete"

End Sub
       


[vojo]

next time, pls capture the line color cell in the image...I try to avoid VBA for this kind of thing...my guess, its something to do
with the quotes around themeguard of the "then" piece of the VBA line (I avoid themes since they are more trouble than value).

You may want to add a bunch of msg windows for debug to ensure the logic and fields are correct.

if doing this all in shapesheet, usually the formula is something like this shape sheet.

user.colorpick = if (strsame(props.project, "my cool project"), RGB (100,150,200), if (strsame(props.project, "your cool project"), rgb(200,150, 100), rgb(0,0,0))        // can have something like 127 if then elses

linecolor = guard(user.colorpick).     // applies the color to lines.

[wapperdude]

The shapesheet is quite powerful as Vojo indicates, and the use of VBA isn't necessarily required. 

But, wrt to your code, this line is incorrectly structured:
If vsoShape.CellsSRC(visSectionProp, 6, visCustPropsValue).Formula = Chr(34) & "INDEX(0, Prop.ProjectType.Format)" & Chr(34) Then

The .formula is used to push into a cell, not retrieve from.  Either use .result(visNone) or .resultstr(visNone).  In this case, probably the 1st option.  However, the following Chr(34) .... Chr(34) construct doesn't do what you want.  That creates a literal string, and not the value implied.  Plus, the Index construct is a shapesheet function, not a VBA function.  You need to grab the value from a cell,  e.g., Prop.projectType.  That is, presumably you're trying to compare one value to another value.

Edit:  The resulting formula s/b something like this:
If vsoShape.CellsSRC(visSectionProp, 6, visCustPropsValue).Result(visNone) = vsoShape.Cells("Prop.projectType").Result(visNone) Then

[Yacine]

From the fact that you're dropping the master from a stencil, I deduce that you need a tailored circle, instead of modifiying already dropped shapes.
Therefor the right approach would be to set up such a master and storeit in the stencil of your choice.

Instead of nested IFs, use an Index/Lookup combination as described here: http://www.visguy.com/2009/10/21/choose-colors-with-shape-data-fields/
If you're in Germany like me, you need to handle the list separators issue as described by Jumpy: http://visguy.com/vgforum/index.php?topic=1303.0

And as a last remark, your IF statements look wrong. There shouldn't be any chr(34) chars around the formula. You can check it by debugging the value of the formula of the cell.
eg debug.print vsoShape.CellsSRC(visSectionProp, 6, visCustPropsValue).Formula

Also instead of the long if/elif list, a nicer SELECT CASE construct could have been used.

Rgds,
Y.

[Simone]

Thanks everyone!  Everyones feedback and suggestions are helpful.

Vojo - I've added another screen shot of the LineColor this is how it is after changing the shape. Next time I will do this - thanks for the feedback and your suggestions.
I'm doing this all via a Macro to change what is in the shapesheet.  The screen shot of the ShapeSheet is after changing the shape. 
I don't want to create more user defined cells I just want to change colors, lineColor, linePattern etc based on certain if statement - unless this can be done via macro?

Wapperdude - thanks, but I don't quite understand your code.  I now understand I have it wrong with retrieval versus pushing a value to a cell. Thanks for this.  But I don't understand your resulting code - how does that relate being able to retrieve the index of the list that is in Prop.projectType??


Yaccine -  You are sort of correct about the stencil.  This is because we have an integration with Visio that it has it's own stencil and we need to use the basic shapes so we adjust the stencil adding them and then change the shape to these basic shapes via the macro.
I am using Visio 2016. I'm not in Germany, in New Zealand but have some great German friends!  Still, I am reading though information on those links you provided.

Also, I found using  an Index/Lookup combination as you suggested works and cuts out the nested if statement.    I used this:

vsoShape.CellsU("LineColor").Formula = "INDEX(LOOKUP(Prop.projectType,Prop.projectType.Format), " & Chr(34) & "THEMEGUARD(RGB(0,176,80));THEMEGUARD(RGB(38,87,153));THEMEGUARD(RGB(112,48,160))" & Chr(34) & ")"

Not sure if the use of Chr(34) should be used but it works.   If you could look at this and let me know that would be great.

So, appreciate the responses!

[wapperdude]

The Prop.projectType, as I understand, uses a dropdown list.  The value cell, which is the default, unspecified cell, contains a singular value, based upon the selection from drop down menu.  Presumably, this is one of the values of interest, hence, the formula retrieves it's value.  The 1st part, presumably, is the value under question.  If these match, then the IF statement evaluates true.  You cannot use the the Index construct directly within VBA.  VBA won't understand it.  So, you need to fetch two values somehow.

[Simone]

Thanks Wapperdude. That makes sense. It also explains why the VBA didn't like the INDEX formula.  This is what initially stumped me!
I could tell from the debug it was the Index VBA would not recognise but didn't know how to deal with this.  I got some good help here.

[Simone]

Also Yaccine you mention

nicer SELECT CASE construct could have been used.

This sounds like SQL coding (which I use alot for SSRS reports) - I didn't think VBA used this Select language - I'll look this up as well. Thanks

[wapperdude]

Since I'm here... Yes.  VBA does support using CASE statement s.  It makes the code more readable and easier to follow than a cascade of IF statements.  Both work.  But 6 months from now, or someone else, it will be easier to follow the logic.