Tags: cell, evaluate, field, microsoft, msdn, myfield, named, priority, prop, refernence, shape, sheet, software, statement, value, values, vba, visio, write

Shape sheet cell refernence, VBA help needed.

On Microsoft » Microsoft Visio

5,629 words with 2 Comments; publish: Sun, 01 Jun 2008 17:21:00 GMT; (30061.52, « »)

How do I write a statement that will evaluate the 5 possible values in my

field named prop.PRIORITY and change the value of the field named

FillForegnd. I want to change the color of this shape based on the value of

prop.PRIORITY which is linked to a database. The 5 possible values for

prop.PRIORTY are:

A-ASAP / Sever Impact

B-Short Term / Hight Impact

C-Long Term / Mod. Impact

D-None / Low Impact

E-Inaccessible

In FillForegnd I have tried using the function:

=IF(Prop.PRIORITY="C-Long Term / Mod. Impact",5,2)

It seems to always return the true value.

From what I've learned, it seems I need to write an If..Then..Else or an

Select Case statement in a macro or a VB module, but I don't know how to do

it. As you can tell, I'm not a programmer and have never used macros or the

VB editor before. Is there a tutorial anywhere in the MSDN web on writing

simple statements and functions? Thanks for any help.

All Comments

Leave a comment...

  • 2 Comments
    • Hello,

      if you want to compare strings, you have to use the STRSAME function. Please

      try this one:

      IF(STRSAME(Prop.PRIORITY,"C-Long Term / Mod. Impact"),5,2)

      In order to check 5 different values, you can interlace the IF statements by

      putting an inner IF into the ELSE part of the outer one.

      Example:

      IF(STRSAME(Prop.PRIORITY,"C-Long Term / Mod.

      Impact"),5,IF(STRSAME(Prop.PRIORITY,"B-Short Term / Hight Impact"),4,2))

      Best regards,

      Markus

      "redwardsjr" <redwardsjr.ms-visio.itags.org.discussions.microsoft.com> schrieb im Newsbeitrag

      news:97F0E287-71AF-437C-98EA-671CD9D3D23F.ms-visio.itags.org.microsoft.com...

      > How do I write a statement that will evaluate the 5 possible values in my

      > field named prop.PRIORITY and change the value of the field named

      > FillForegnd. I want to change the color of this shape based on the value

      of

      > prop.PRIORITY which is linked to a database. The 5 possible values for

      > prop.PRIORTY are:

      > A-ASAP / Sever Impact

      > B-Short Term / Hight Impact

      > C-Long Term / Mod. Impact

      > D-None / Low Impact

      > E-Inaccessible

      > In FillForegnd I have tried using the function:

      > =IF(Prop.PRIORITY="C-Long Term / Mod. Impact",5,2)

      > It seems to always return the true value.

      > From what I've learned, it seems I need to write an If..Then..Else or an

      > Select Case statement in a macro or a VB module, but I don't know how to

      do

      > it. As you can tell, I'm not a programmer and have never used macros or

      the

      > VB editor before. Is there a tutorial anywhere in the MSDN web on writing

      > simple statements and functions? Thanks for any help.

      >

      #1; Sun, 01 Jun 2008 17:22:00 GMT
    • The following is from http://www.mvps.org/visio/ShapeSheet.htm called

      "Colour based on Status" by Mark Nelson

      Put this formula in the FillForegnd cell.

      =Lookup(Prop.Status,"Black;White;Red;Green;Blue;Ye llow")

      (A formula error will occur if the custom property, in this case Status,

      does not already exist.) Lookup will match the value of the custom property

      Status to the list shown above. The value returned by Lookup will be a

      number starting with 0 which corresponds to the position of the matched

      value in the list. The list shows Visio's numbering sequence for the

      colours.

      If the property choices are "Warning", "Caution" and "Clear" with

      corresponding colours. In the Lookup formula, replace the words "Red",

      "Yellow" and "Green" with "Warning", "Caution" and "Clear". You must leave

      the other items in as place holders to get the correct colour position. The

      final formula would be:

      =Lookup(Prop.Status,"Black;White;Warning;Clear;Blu e;Caution")

      John... Visio MVP

      Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm

      Need VBA examples? http://www.mvps.org/visio/VBA.htm

      Common Visio Questions http://www.mvps.org/visio/common_questions.htm

      "redwardsjr" <redwardsjr.ms-visio.itags.org.discussions.microsoft.com> wrote in message

      news:97F0E287-71AF-437C-98EA-671CD9D3D23F.ms-visio.itags.org.microsoft.com...

      > How do I write a statement that will evaluate the 5 possible values in my

      > field named prop.PRIORITY and change the value of the field named

      > FillForegnd. I want to change the color of this shape based on the value

      > of

      > prop.PRIORITY which is linked to a database. The 5 possible values for

      > prop.PRIORTY are:

      > A-ASAP / Sever Impact

      > B-Short Term / Hight Impact

      > C-Long Term / Mod. Impact

      > D-None / Low Impact

      > E-Inaccessible

      > In FillForegnd I have tried using the function:

      > =IF(Prop.PRIORITY="C-Long Term / Mod. Impact",5,2)

      > It seems to always return the true value.

      > From what I've learned, it seems I need to write an If..Then..Else or an

      > Select Case statement in a macro or a VB module, but I don't know how to

      > do

      > it. As you can tell, I'm not a programmer and have never used macros or

      > the

      > VB editor before. Is there a tutorial anywhere in the MSDN web on writing

      > simple statements and functions? Thanks for any help.

      >

      #2; Sun, 01 Jun 2008 17:23:00 GMT