News:

BB code in posts seems to be working again!
I haven't turned on every single tag, so please let me know if there are any that are used/needed but not activated.

Main Menu

Org Chart: VBA to change the colour of boxes based on values in the user data

Started by miless2111s, September 17, 2024, 04:45:31 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

miless2111s

I have an org chart which is generated from an Excel table.  In the table and chart we have a field "Source" to show if the role is filled internally or externally.   

I would like to be able to run a macro which then colours the internal and external resources different colours.  I would also like to be able to shade the current vacancies (name = vacant) a lighter colour.

I have the following code which makes the changes to the shapes when you look at the shape sheets:

Sub ReColour()
Dim shp As Visio.Shape
Dim pagshape As Visio.Shape
Set pagshape = Visio.ActivePage.PageSheet

For Each shp In Visio.ActivePage.Shapes
    If shp.CellExistsU("prop.source", visExistsAnywhere) <> 0 Then 'Debug.Print shp.CellsU("prop.source").ResultStr(visNone)
        If shp.CellsU("prop.Source").ResultStr(visNone) = "HO - Full time (back filled)" Then
            shp.CellsU("Fillforegnd").FormulaU = "=THEMEGUARD(RGB(250,100,50))"
            shp.CellsU("FillBkgnd").FormulaU = "=THEMEGUARD(RGB(0,100,150))"
            shp.CellsU("Fillpattern").FormulaU = "27"
            Debug.Print shp.ID
        End If
    End If
   
Next shp

End Sub

However the actual shape doesn't change colour, even when I manually mess around with the contents of the shape sheet :(   I am so confused.

One thing I did spot was that the shape sheet has a field user.backFillColor which is set to sheet.25!FillForegnd where 25 is the ID of the shape.  This isn't the same as =fillForegnd and I am very confused as to where sheet25! is pointing to as another shape (ID 155) on the same sheet is pointing to sheet155! so it doesn't appear to be a different sheet :(

any help will be gratefully received as this feels like something that should be easy but the shape are just behaving very strangely.

Nikolay

The modern org chart shapes are composite shapes (they consist of more than one shape, grouped together).
The internal shapes are protected from group formatting to prevent unintentional damage to the shapes.
You may need to remove that protection to be able to change the color.

Your code changes the fill of the "group" shape and does not affect the actual appearance (that is defined by the child shapes).



miless2111s

Quote from: Nikolay on September 17, 2024, 10:03:54 PMThe modern org chart shapes are composite shapes (they consist of more than one shape, grouped together).
The internal shapes are protected from group formatting to prevent unintentional damage to the shapes.
You may need to remove that protection to be able to change the color.

Your code changes the fill of the "group" shape and does not affect the actual appearance (that is defined by the child shapes).


Thank you for this, a couple of questions spring out from this:
1) When I click on a composite shape, or one of the components, how do I tell which is being displayed in the shape sheet?
2) I was expecting the macro to look at the individual / component shapes - is it not doing this?
3) If the macro is changing the component sheets (which I suspect it might be) how do I cause the overall grouped sheet to change - i.e. how can I trace back up the component tree to the higher level to effect the change which has already been made at the component level?

When I use the Drawing Explorer to select the individual elements of a manager belt shape and then click on Protection in the Shape Design ribbon none of them seem to be protected from Formatting or Group Formatting, with the exception of the image which is protected from Group Formatting.

Many thanks

Miles

Surrogate

Quote from: miless2111s on September 19, 2024, 07:53:42 AM1) When I click on a composite shape, or one of the components, how do I tell which is being displayed in the shape sheet?
You can see Shape's ID in top center part of ShapeSheet's window

miless2111s

Quote from: Surrogate on September 19, 2024, 08:43:29 AM
Quote from: miless2111s on September 19, 2024, 07:53:42 AM1) When I click on a composite shape, or one of the components, how do I tell which is being displayed in the shape sheet?
You can see Shape's ID in top center part of ShapeSheet's window

Awesome, thank you.  That's really useful as it stays in view when scrolling through the settings.

Browser ID: smf (possibly_robot)
Templates: 4: index (default), Display (default), GenericControls (default), GenericControls (default).
Sub templates: 6: init, html_above, body_above, main, body_below, html_below.
Language files: 4: index+Modifications.english (default), Post.english (default), Editor.english (default), Drafts.english (default).
Style sheets: 4: index.css, attachments.css, jquery.sceditor.css, responsive.css.
Hooks called: 253 (show)
Files included: 32 - 1207KB. (show)
Memory used: 1114KB.
Tokens: post-login.
Cache hits: 13: 0.00189s for 26,741 bytes (show)
Cache misses: 3: (show)
Queries used: 17.

[Show Queries]