Hey everybody,
First of all, I'm new here, I hope I can get some help.
I've been asked to make an Organization Chart for over 700 people of this company, well at first it wasn't hard to make the chart, but there are some bits of VBA coding needed, which I don't quite understand, thus making me post.
Only thing, I need help with is the automatic colors dealt within the chart.
It should make it deal automatic colours to ea job. ie;
all accountants are green
all managers are red
This has to be automatic! (VBA coding)
I hope someone can help me with this, I've been trying to figure this out for the past 2 days and, to be honest I didn't do well.
Great thanks to anyone who will help me,
Aliq
Quote from: Aliq on June 01, 2011, 11:12:10 AM
This has to be automatic! (VBA coding)
Why? Sth. like that is easy done in the ShapeSheet! If the drawing is already complete you could try editing the master in the document stencil....
if
a.) the jobs are stored as custom properties/shape data
b.) you are using v2007 (or above) pro (or above)
why not just use a data graphic (3 minutes at most and no code needed)
al
Quote from: Jumpy on June 01, 2011, 11:40:02 AM
Quote from: Aliq on June 01, 2011, 11:12:10 AM
This has to be automatic! (VBA coding)
Why? Sth. like that is easy done in the ShapeSheet! If the drawing is already complete you could try editing the master in the document stencil....
I've never used Microsoft Visio, so I'm not all into it, if you could explain how I could do it in a ShapeSheet? because it has to be automated, like the jobs are not that different and they want it in different colors.
Quote from: aledlund on June 01, 2011, 12:22:55 PM
if
a.) the jobs are stored as custom properties/shape data
b.) you are using v2007 (or above) pro (or above)
why not just use a data graphic (3 minutes at most and no code needed)
al
A) Every person and jobs, phonenumbers etc. are filled in an Excelsheet.
B) I'm using Microsoft Visio 2003.
I see there are many different ways to make it, as the person above you said something about a ShapeSheet.
I have to make it as easy as possible so the people can update it and mess with it after I'm gone. (I'm an intern in a big company, 4 weeks left)
I'll have to get some help here to decide which one is easier to use, and if it colors each job automaticly, because thats the point.
Thanks allot for your replies!
Aliq
you could even make this simpler
Create a stencil where you have shapes by color for each role. Pick and drop as needed.
I agree that there is some learning to do with shapesheet...but once you get the hang of it, you can implement what you want in 10 min.
this will get you started
Behavior: right click
got to shape sheet to see how down
oh yea...the shape
Quote from: vojo on June 01, 2011, 01:37:29 PM
you could even make this simpler
Create a stencil where you have shapes by color for each role. Pick and drop as needed.
I agree that there is some learning to do with shapesheet...but once you get the hang of it, you can implement what you want in 10 min.
I don't think you understood what I was trying to accomplish.
I need to make a organization chart, with jobs and names etc. taken out of an Excel file, but ea job gets a different color. (colors are done job-wise)
This has to happen automaticly, and I should be able to change the colors later on too.
Thanks for your help though! I'm trying to read on the Microsoft site how to use these Sheets.
Aliq
Is the color by job a new, additional requirement? Initial post indicated color by position.
If you are importing the Excel data via Org Chart Wizard, I think the wizard will clobber any shapesheet edits that you do. So, the coloring thing will have to be done after the fact, i.e., with code.
I don't have the version that Al refers to, so, his datagraphic approach may work.
Wapperdude
at v2003 there is an add-in 'color-by-value' which has become more formalized as the years have gone by.
al
Quote from: wapperdude on June 01, 2011, 11:19:17 PM
Is the color by job a new, additional requirement? Initial post indicated color by position.
If you are importing the Excel data via Org Chart Wizard, I think the wizard will clobber any shapesheet edits that you do. So, the coloring thing will have to be done after the fact, i.e., with code.
I don't have the version that Al refers to, so, his datagraphic approach may work.
Wapperdude
Yeah, I import the Excel data through the Org. Chart wizard, after thats done, I want to either have a way to make all jobs different colored, or a VBA script that I can let run.
So I'm still kinda stuck.
Quote from: aledlund on June 01, 2011, 11:28:32 PM
at v2003 there is an add-in 'color-by-value' which has become more formalized as the years have gone by.
al
Where can I find this add-in?
Thanks for the replies,
Aliq
tools => add-ons => building plan => color-by-value
al
With Code:
Dim shp as Visio.Shape
For Each shp in ActivePage.Shapes
If shp.CellExists("Prop.Job",false) then
Select Case shp.Cells("Prop.Job").ResultStr("")
Case "Manager": shp.Cells("LineColor").FormulaU = 5
Case "Toilett-Lady": shp.Cells("LineColor").FormulaU = "RGB(255,0,0)"
.
.
.
End Select
End If
Next
untested.
"Prop.Job" must be the name of the Job proposition.
Istead of LineColor you can change the FontColor or ForegroundColor, but I have no Visio at the moment, so I didn't know the right ShapeSheet names.
"RGB(255,0,0)" could be "RGB(255;0;0)" depending on Visio language.
.
.
.
But it's a start
hth Jumpy
Quote from: aledlund on June 02, 2011, 07:20:34 PM
tools => add-ons => building plan => color-by-value
al
Thanks allot, I'll look this up tomorrow since its past midnight here.
Quote from: Jumpy on June 03, 2011, 06:17:16 AM
With Code:
Dim shp as Visio.Shape
For Each shp in ActivePage.Shapes
If shp.CellExists("Prop.Job",false) then
Select Case shp.Cells("Prop.Job").ResultStr("")
Case "Manager": shp.Cells("LineColor").FormulaU = 5
Case "Toilett-Lady": shp.Cells("LineColor").FormulaU = "RGB(255,0,0)"
.
.
.
End Select
End If
Next
untested.
"Prop.Job" must be the name of the Job proposition.
Istead of LineColor you can change the FontColor or ForegroundColor, but I have no Visio at the moment, so I didn't know the right ShapeSheet names.
"RGB(255,0,0)" could be "RGB(255;0;0)" depending on Visio language.
.
.
.
But it's a start
hth Jumpy
Ah, if this works, then thats what I needed, thanks allot mate!
I'll test this tomorrow and get back to you.
I'll try all differnt ways, and find the best and easiest way to edit it later on for the people who take this over from me.
Thanks allot guys!
Quote from: Jumpy on June 03, 2011, 06:17:16 AM
With Code:
Dim shp as Visio.Shape
For Each shp in ActivePage.Shapes
If shp.CellExists("Prop.Job",false) then
Select Case shp.Cells("Prop.Job").ResultStr("")
Case "Manager": shp.Cells("LineColor").FormulaU = 5
Case "Toilett-Lady": shp.Cells("LineColor").FormulaU = "RGB(255,0,0)"
.
.
.
End Select
End If
Next
untested.
"Prop.Job" must be the name of the Job proposition.
Istead of LineColor you can change the FontColor or ForegroundColor, but I have no Visio at the moment, so I didn't know the right ShapeSheet names.
"RGB(255,0,0)" could be "RGB(255;0;0)" depending on Visio language.
.
.
.
But it's a start
hth Jumpy
I'm trying to get your code to work, but without any succes.
I don't get what I have to put in something instead of the Job Proposition.
Aliq
Could you post a littel example of your drawing, a few shapes, as to see their internal structure, but with non sensitive data?
Quote from: Jumpy on June 08, 2011, 06:45:50 AM
Could you post a littel example of your drawing, a few shapes, as to see their internal structure, but with non sensitive data?
Alright, it's not sensitive data at all, as I didn't use the real Excel sheet.
This one was made with Visio's wizard and is a test file.
Your shapes don't have "Prop.Job", they have "Prop.Functieomschrijving" or "Prop.Title", not sure if you want the title or the funktionsbeschreibung to determine the colors.
You query the shape by asking for the value of the cell named in the argument:
val = shp.Cells("somecellname").ResultStr(Vis.visUnitCodes.visUnitsString)
then you check val to determine which color to apply.
Quote from: Visio Guy on June 08, 2011, 09:42:49 AM
Your shapes don't have "Prop.Job", they have "Prop.Functieomschrijving" or "Prop.Title", not sure if you want the title or the funktionsbeschreibung to determine the colors.
You query the shape by asking for the value of the cell named in the argument:
val = shp.Cells("somecellname").ResultStr(Vis.visUnitCodes.visUnitsString)
then you check val to determine which color to apply.
Haha, first of all nice dutch + german ;)
I need it through the Prop.Functieomschrijving, which is Prop.Job
Aliq
Than this should be the little change you need to make in the code.
.
.
.
If shp.CellExists("Prop.Functieomschrijving",false) then
Select Case shp.Cells("Prop.Functieomschrijving").ResultStr("")
.
.
.
Quote from: Jumpy on June 08, 2011, 12:29:06 PM
Than this should be the little change you need to make in the code.
.
.
.
If shp.CellExists("Prop.Functieomschrijving",false) then
Select Case shp.Cells("Prop.Functieomschrijving").ResultStr("")
.
.
.
Ok it works, thanks allot mate!
I can just add cases underneath the one case right?
And I can change the "LineColor" to "FillBkgrnd" << not sure but I read it somewhere gonna search for it.
Aliq
Yes and Yes, but I think FillBkgnd without "r". Or FillForegnd.
I never remember what's to use, because one is only used with FillPatterns.
Quote from: Jumpy on June 08, 2011, 02:49:55 PM
Yes and Yes, but I think FillBkgnd without "r". Or FillForegnd.
I never remember what's to use, because one is only used with FillPatterns.
Thanks allot everyone, specially Jumpy!
Now to get some buttons and stuff working, might need help and see me post something else, haha.
Aliq
Don't know from where in Orange Oranje County you hale, but as live near Venlo, you can see it as neighborly help ;D
Quote from: Visio Guy on June 08, 2011, 09:42:49 AM
Your shapes don't have "Prop.Job", they have "Prop.Functieomschrijving" or "Prop.Title", not sure if you want the title or the funktionsbeschreibung to determine the colors.
You query the shape by asking for the value of the cell named in the argument:
val = shp.Cells("somecellname").ResultStr(Vis.visUnitCodes.visUnitsString)
then you check val to determine which color to apply.
How do you get the EXACT cell names?
I've been trying, (sorry this is in C++) but nothing happens (note I tried get_CellsU) as well.
CVCell backgroundCell = next.get_Cells("FillBkgnd");
backgroundCell.put_Formula(child->m_color == CASSTVisioIF::eWhiteColor
? CASSTVisioIF::m_SupportedColorFormulaWhite
: CASSTVisioIF::m_SupportedColorFormulaGray);
I was looking at the MSDN reference and it doesn't help alot but it called the cell "FillBkgnd"), here :
https://msdn.microsoft.com/en-us/library/office/jj945060.aspx?f=255&MSPPError=-2147217396
/* So far, in my 20 years of s/w experience, this is the most difficult API I've ever used. Part of the problem
involves the lack of documentation.
*/
I'm not that much in C#, but in VBA shp.cells(CellName) always works.
I do get the name by opening the shapesheet of a shape, then edit a random cell (eg one in the user section), while in edit mode (F2), I click on the aimed cell. Visio inserts the cell's name in the cell being edited.
HTH,
Y.
Fill Format section cells
Quote from: Yacine on February 10, 2016, 08:41:56 PM
I'm not that much in C#, but in VBA shp.cells(CellName) always works.
I do get the name by opening the shapesheet of a shape, then edit a random cell (eg one in the user section), while in edit mode (F2), I click on the aimed cell. Visio inserts the cell's name in the cell being edited.
HTH,
Y.
Quote from: Surrogate on February 10, 2016, 08:48:31 PM
Fill Format section cells
thanks
I discovered that the shape I was using, 3D rectangle was not using these attributes. however, fill fore color worked for
Rectangle and rounded rectangle. I guess the former is using shading. y
The line color worked on the latter shapes but not the former
Anyway, found that by trial and error. look forward to playing more in GUI to try your tips