ComboBox in visio

[RedtoGreen]

and here is my little problem...

I am creating a large org chart within Visio 2003.  I currently have the code

=IF(STRSAME(Prop.Trained,"Yes"),RGB(205,133,63),RGB(255,255,255))

in the "FillBkgnd" format property in the shapesheet for the Executive/manager/position shape masters, and this is working fine.  I also have a combobox on the org chart which contains the options "Trained", "Nationality", and "Normal".  So far all is good.

My question, or at least what I am trying to figure out how to do if it's possible.... is that I would like the above code only to be applicable if "Trained" is selected via the combox box.  If the combobox option is "Nationality", I would like the code (as below) to be run.  If the option selected is "Normal" then simply "RGB(100,100,100)" would be applicable.

=IF(STRSAME(Prop.Nationality,"British"),RGB(255,0,0),RGB(0,0,0))

I was thinking something along the lines of "IF combobox1.text="Trained" then *THIS* else *THAT*" but visio doesn't like this!  If anyone out there has any ideas about this I would appeciate that, or if I've not explained things clearly then let me know too!

Thanks,
RedtoGreen

[RedtoGreen]

Found this code on another site... It comes *oh so close* to doing what I want... but it doesn't search the custom properties which is what I need.  Any ideas?  Any method of achieving the same results would be fine by me.

Private Sub CommandButton1_Click()

    Dim vsoPage As Visio.Page, vsoShape As Visio.Shape
    Dim vsoCharacters As Visio.Characters
   
    For Each vsoPage In ThisDocument.Pages
        For Each vsoShape In vsoPage.Shapes
            Set vsoCharacters = vsoShape.Characters
                        If vsoCharacters = "Yes" Then
                vsoShape.CellsSRC(visSectionObject, visRowFill, visFillForegnd).FormulaForceU = "RGB(255,0,0)"
            End If
            If vsoCharacters = "British" Then
                vsoShape.CellsSRC(visSectionObject, visRowFill, visFillForegnd).FormulaForceU = "RGB(0,255,0)"
            End If
            Next
    Next

End Sub

[wapperdude]

As a reference for using cellsSRC, check out this article:  http://msdn.microsoft.com/en-us/library/ms425971.aspx.  The article contains hyperlinks which you will need to explore.

For your specific case, the prop section is:  visSectionProp

An example that might be adapted for your use:

Code Select Expand

Sub FindEngineer()
    Dim shape As shape
    Dim i As Integer, j As Integer, nRows As Integer
   
    For Each shape In ThisDocument.Pages(1).Shapes
        If shape.Master <> "Dynamic connector" Then
            nRows = shape.RowCount(Visio.visSectionProp)                                                                           'number of rows in Shape Properties section
            For i = 0 To nRows - 1                                                                                                  'Search all rows for value cell = Engineer
                If shape.CellsSRC(Visio.visSectionProp, i, 0).ResultStr(Visio.visNoCast) = "Engineer" Then
                    'shape.CellsSRC(visSectionObject, visRowFill, visFillForegnd).FormulaForceU = "RGB(255,0,0)"     
                    shape.CellsU("LineWeight").FormulaForceU = "4 pt"          'This makes the Engineer cell heavy line
                End If
            Next i
        End If
    Next
End Sub

HTH
Wapperdude

[Jumpy]

Instead of a Combo-Box and VBA, can't you use the ShapeData Section of the ShapeSheet of the page?
Make there a ShapeData-Row named Prop.Selection, make it a list (1) with the following options in Format: "Normal;Nationality;Trained".

Then to circumvent the horrible STRSAME function create a row in the User Section of the ShapeSheet of the page as follows:
User.SelectionNumber with the formula: LOOKUP(Prop.Selection,Prop.Selection.Format).
Here you get numbers 0-2, depending on your selection.

Now in the FillBkgnd Cell of the relevant shapes:

=IF(ThePage!User.SelectionNumber=1,IF(STRSAME(Prop.Nationality,"British"),RGB(255,0,0),RGB(0,0,0)),IF(ThePage!User.SelectionNumber=2,IF(STRSAME(Prop.Trained,"Yes"),RGB(205,133,63),RGB(255,255,255)),RGB(100,100,100)))

It may be that some ( or ) are missing, but aside from that, it should work.
By the way: what's the english word for ( and ) bracket???

But your shapes need to have both prop rows:
Prop.Trained and Prop.Nationality

[vojo]

should work fine.   I do this all the time...even extending this approach for context sensitive drop downs to get around the 25 custom props limit.
Can use an index to set/hide custom props before calling docmd(1312).

[RedtoGreen]

Thank you all for your responses.  They look useful.  Sorry about the delay in responding... Christmas getting in the way and all that!

I shall implement them shortly, and report back if they work or if I have any further problems!

[RedtoGreen]

wapperdude - I've tried your suggestion.  It works well, with minor modifications.  Thanks.  I've included the code I've used just for information.  A series of command buttoms (each slight variations on below) would work, but doesn't look quite as good as a combobox (or similar), so I've also tried to make Jumpy's suggestion work...

To that end, I have a little question...  I added the Prop.Selection to the custom properties of the page shapesheet, as this was the closest I could see to any ShapeData section.  Certainly the only section with "prop.??" in.  Assuming I did that right, how do I (or at least, the user) then select which of these options within the format cell, he wants to select?  Do I need to add something to allow users to easily select between "Normal" and "Nationality"?  Perhaps even a combobox to select these... I am aware I can select the "custom properties" from the "shape" menu... but I was after a way of making this more user-friendly?

Also, I've tried putting the code

Code Select Expand

=IF(ThePage!User.SelectionNumber=1,IF(STRSAME(Prop.Nationality,"British"),RGB(255,0,0),RGB(0,0,0)), RGB(255,255,255)) into the FillBackground cell, but it simply says "Error In Formula".  Any ideas?  I suspect perhaps the "ThePage!" part is incorrect, but am not sure.

RedtoGreen

ps Not sure if I have it officially right... but I always just call "(" open bracket and ")" close bracket.  "[" would be open square bracket, and "{" open curly bracket.

Code Select Expand

Private Sub CommandButton1_Click()
   Dim shape As Visio.shape
   Dim page As Visio.page
   Dim i As Integer, nRows As Integer
   
   For Each page In ThisDocument.Pages
   For Each shape In page.Shapes
       'If shape.Master <> "Dynamic connector" Then
           nRows = shape.RowCount(Visio.visSectionProp)                  'number of rows in Shape Properties section
           For i = 0 To nRows - 1                                                   'Search all rows for value
               If shape.CellsSRC(Visio.visSectionProp, i, 0).ResultStr(Visio.visNoCast) = "Yes" Then
                   shape.CellsSRC(visSectionObject, visRowFill, visFillForegnd).FormulaForceU = "RGB(255,100,255)"
               End If
           Next i
       'End If
   Next
   Next
End Sub

[Jumpy]

The ShapeData section is what I meaned. If you have a ShapaData Field named XYZ and you look in the ShapeSheets, you'll see that the name of the row is Prop.Row_1 for example and the label ist XYZ. But I like to work with row names to so I rename them, in this example to Prop.XYZ.

Now how to Access the ShapeDate. There are 3 ways:
The one you have already mentioned: Via the Shape Menu or the Data Menu.

Then the option I always use: I (have always) open the ShapeData-Window (in the View menu). That is a either free floating or somewhere docked window which shows the ShapeData of a currently selected Shape or if you click on the page, so deselecting all shapes, it shows the ShapeData from the page, which is what we want.
And in this window the ShapeData can be selected like in a combobox

The last option is to add the Action Section to the ShapeSheet of the page. In this section each row makes an entry in the right-click-menu of a shape or the page.
In youre case you should write a String like "Select Property" or sth. like that in the "Menu" cell of the action row and in the "Acion" cell you write =DOCMD(1312)
This opens the ShapeData form and is a shortcut if you want, for option one, mentioned above.


-------

This last option made me think about an alternative idea for your problem:
You can make more entries in the Action Section one for each option you wanted in a combobox. You can make it so, that the one currently active is not visible, so that the user can via rightclick only choose between the other options. This would be a way without combobox or sth. like that, and would instead use the right-click-menu.
I've you are interested I could elaborate on this.

------------

Don't know what's wrong in the If-Formula. The number of brackets seems right. If User.SelectionNumber exists in the ShapeSheet of the page and if exists a Prop.Nationality in the shape it should work.

-------------

Thanks for the bracket clarification. I feared it was the word for the things kids have in there mouth to correct their teeth.

[RedtoGreen]

Well, the good news is I'm moving on...

Next problem.. The IF formula is now working (sortof).  At least it no longer gives an error (I was previously trying to enter the IF formula before I had defined the custom properties).  However, it now it seems to assume that UserSelectionNumber is always 0, no matter what changes I make to inputs/custom properties etc. 

Any thoughts as to why it might be doing this?  The User.SelectionNumber section seems to be where it is falling down, but quite how, I'm not sure.



and that right-click menu suggestion you made sounds like a good one to me, and I like it.  However, what I have above would be more than ok, so no need to spend too much time typing it all out unless it's something you can write about without too much trouble on your part!

[RedtoGreen]

All is good with the world.  I've just realised what was wrong.  I had the LOOKUP formula in quotes.  I removed these, and it worked perfectly!  It was like that moment when you realise all you'd done wrong was forget to put a semi-colon at the end...

Thanks for suggestions, now just to implement them all in a full-scale version rather than the mini-versions I have been using so far..!

[Jumpy]

Glad it works. However, here comes the Right-Click-Version.

All the following is in the ShapeSheet of the page:

You need One User definded cell, I name it User.Select. The Value for starters is 0.
The value changes from 0 to 2 and stands for: 0 = normal, 1 = nationality, 3 = trainined.
Your formulas in the format cell would have to work with this User cell (the same as it does with the User.SelectionNumber now).

The nice trick is to change this number now via right-click:
In the Acion-Section of the ShapeSheet make 3 rows and fill them as follows:

Name / Action / Menu / ... / SortKey / ... / Invisible

Actions.Normal / =SETF(GETREF(User.Select),0) / "Normal" / "10" / =If(User.Select=0,True,False)
Actions.Nationality /  =SETF(GETREF(User.Select),1) / "Nationality" / "20" / =If(User.Select=1,True,False)
Actions.Trained /  =SETF(GETREF(User.Select),2) / "Traiend" / "30" / =If(User.Select=2,True,False)

Because of the Invisible-Cell only the two currently not selected possibilities are visible.
Didn't try it out, but if I made no grave errors, it should work.

[RedtoGreen]

Will try that soon (perhaps).  In the mean time, I have...

=IF(ThePage!User.SelectionNumber=1,IF(STRSAME(Prop.Nationality,"British"),RGB(255,0,0),RGB(0,255,0)),RGB(0,0,255))

in the FillBackground cell of one particular shape of the org chart (which I was using to quicky test all of the above).  It works perfectly, and it all works as it should (namely the individual shape changes colour when I select a different option).

However, I am now trying to change the shape-master to the above code, and when I copy-and-paste the exact same code into the shape master in the stencil, it gives me the Error In Formula error.  I have no idea why.  I'm copying the code into the same cell, but simply the master instead of an individual shape.  I'm using a blank template, that has all the relevant custom properties, and all the code in the page-shape-sheet as detailed above.  hmmm...  Any ideas anyone?

[Jumpy]

When you edit a master the master is on a pseudo-page to. This page has a ShapeSheet to and this page of the master needs the custom properties, too, for the formula to work.

Two ways around that:
a) Drop the master on your page. Edit it there. Drop it back to the stencil, thus creating a new master.
(When you look inside the new master it may be, that inside the formula there is sth. like Ref() which shows that a reference is missing, but when you drag this new master to a page, that has the correct custopm properties, the reference will be back and working.

b) Go to the ShapeSheet of the page of the master, when you are in master-edit-mode, and create there the Custom Properties.
(This has one advantage: When you drop the master on a page, which doesn't have the correct custom properties in the page, then they will be created auomatically by Visio. At least in theory, didn't try it out, jet.).

The backround to this is, that a master in a stencil is not truly a shape but a page. So User. und Prop. cells of the page of the master and styles that aren't on a page where a master is dropped, will be created there by Visio.

[RedtoGreen]

I've done it using method a.  I now have my new master (for now, named "Master.2") in the stencil, along with all the original shapes (ORGCH_M.VSS).  The latest problem I'm having is how do I get Visio (specifically the org chart wizard) to use this new shape instead of the default (in this case, the default manager shape)? 

I've tried re-naming Master.2 to Manager, and deleting the original, but this simply results in Visio changing the properties of Master.2 to the original's properties as soon as I drop it onto the main page, despite the original Manager no longer existing within the stencil.  When the original Manager is still in the stencil, Master.2's properties are kept when I drop it onto the page - however the wizard then still uses the original Manager when it is run. 

With any luck, this will be the last problem I have...

[vojo]

Do approach A in the shape in the document stencil.    file==>shape===>document stencil.
That will catch all shapes in all pages of the current document.
Need to do with each doc already created.
Use the new master.2 in stencil for future docs

(when you drag a shape on to a page, visio copies the shape from stencil X into the document stencil).