working with shapes

[ioncila]

Hi all

Where do I find a tutorial or examples to show me how to create bars for gantt chart in excel, based on date values?

Thanks

[rylo]

Hi

Try having a look at the Peltier Technical Services Inc site on charts. There's a heap of really good chart items there.

http://peltiertech.com/Excel/Charts/...tml#GanttChart

rylo

[shg]

And the home of our very own Excel chart god, http://andypope.info/

[ioncila]

Thanks for the tips but I was thinking about something different. What I had in mind is to create bars directly in a range of cells based on date criteria.

[Andy Pope]

Have a look at in-cell charting
http://peltiertech.com/Excel/Charts/...ulaCharts.html

Also look at conditional formatting.

[ioncila]

Have a look at in-cell charting
http://peltiertech.com/Excel/Charts/...ulaCharts.html

Also look at conditional formatting.

Thanks for the tip but I already tested those options. What I'm looking for is something similar to project management. I know it can be done in excel because I've seen a few examples in the web.

I use MS Project for bigger projects, but for some plannings it would be useful to do it directly in excel.

There must be some tutorial to teach how to do that.

Greetings

[Andy Pope]

As I said you can use conditional formatting.

[ioncila]

As I said you can use conditional formatting.

Hi Andy
Sorry for insisting, but I didnt present the correct question since the beginning.
I'm using now conditional formatting based on VBA, for I can have more than 3 options.

What I would really like to learn is how to built bars based on shapes (rectangules).

Thanks

[Andy Pope]

This will generate the bars using shapes.

Please Login or Register  to view this content.

[ioncila]

Thank you very much Andy

This is a new beginning for me. Now I'm going to try to adapt this to my file and will show when finished.

Thank you again

[ioncila]

Hi Andy
I sent all weekend trying to adapt your code to this file attached. All changes I made didnt go through. They always stopped on error in the code line with match function. My vanity thinking that my very little knowledge would be enough to develop your code.
Would it be too much begging you to look once to the file?
For more than one level, is the code look too complex?

Thank you very much

[Andy Pope]

Try this. See comments in code

Please Login or Register  to view this content.

[ioncila]

Hi

Thank you very much for your help.
This works almost fine, almost perfect. Some bars dont match with some end dates, but I will try to fix that.
Other thing I'm going to try is give diferent colors to tasks with diferent levels
(dreaming awake?)

I'm learning much more in foruns than the VBA course I took last year

Thank you very much for your time

Ioncila

[ioncila]

I made some little improvements with the code that Andy Pope provided (Thank you very much). But there is 2 or 3 things that I cant solve and I would wish to:

1. Position of some bars dont match with End date values;

2. If each column of the timeline represents a week (7 days), Is there a simple way to position the bar in cell range to match with start and end date values?

3. How to add a new bar showing the task progress (%complete)?

Maybe this is asking too much but I'm very hopefull to get some help.

Thank you all and, in particular, thank you very much Andy.

Here's an attachement

[Andy Pope]

Please Login or Register  to view this content.

[ioncila]

Andy
I dont have adjectives to congratulate your work and knowledge.

Thats exactly what I was looking for.

Thank you for your sunday time and all the help you provided to me.

ioncila

[ioncila]

Hi again

I saw a piece of code from Andy in another thread:

http://www.excelforum.com/2055217-post4.html

which can draw shape connectors between 2 cells (I put code in the attached).

Now, is it possible to convert this connector into something like a predecessor and link 2 or more task bars and show a critical path (see attached)?

Thank you all

[shg]

I use MS Project for bigger projects, ...

You seem to be trying to reinvent Project one piece at a time.

[ioncila]

You seem to be trying to reinvent Project one piece at a time.

Because Excel is so versatile, most of my projects are based on it, except scheduling manegement and other tasks that I thought MSProject was the only and the right way to do it.

A few months ago, I did realize that Excel could offer, in a light way, some tasks functions of MSProject which are enough to my needs. I simply dont have the knowledge (enough at least) to do the improvements I need. But searching in forums and asking some questions, I did learn so much lately than the years passed.

Not trying to reinvent anything. You know that when you think if something can be done and you get the answer to that, the next second you are already thinking if it is possible to do another thing and so on.

However, before posting questions or opening threads, I try harder to make things work out by my own. Unfortunelly, sometimes I cant (specially if it takes to work in VBA).

Other thing: I dont take any credits for the help I get. In all of my works I post links to forums, threads or people's websites who did provide the improvements I've done in my projects. That's my humble way to thank you all.

ioncila

[Andy Pope]

Can you manually add the connectors to illustrate how the numbers in column D should work

[ioncila]

Can you manually add the connectors to illustrate how the numbers in column D should work

Sorry, is that a question or a suggestion?

[ioncila]

Hi

It would work as the attached file shows.

Column "ID" gives the row ID (that can be the real row number and if it is so, this column not necessary) and Column "PREDEC" shows the ID of the task dependency.

[Andy Pope]

Sorry I can not yet see the logic in your lines.

Take row 32. Why does the arrow go to 38 instead of 37?

You already have the code for creating the shape all you need to do is determine the start and finish position

[ioncila]

...all you need to do is determine the start and finish position

I cant determine start and finish position of the bars, because of their relative position in each cell, dont know how to create variable for that

[Andy Pope]

You can set a reference to the shape using this syntax.

Please Login or Register  to view this content.

which then allows you to use the Left/Top/width etc properties.

[ioncila]

Hi
Following your suggestion, I added a piece of code for connectors, but I think it looks confuse or there's something missing

Please Login or Register  to view this content.

[Andy Pope]

Just a quick clean up of your code to define the variables.

Please Login or Register  to view this content.

The problem is your code is not using any task against which to align the connector.

Some how your code needs to determine which 2 tasks are relevant for each precedent. You can then use those variables instead of rngA and rngB.

[ioncila]

So many dificulties to create those variables.
Is this a step foward? or behind?
I think my issue is in determinating "varS" and "varE" variables and reading their bar shapes. I leave "rngA" and "rngB" to refer the start and finish of the connector.

Please Login or Register  to view this content.

[Andy Pope]

Forget the drawing and sizing of the connector for a moment.

The tasks you need the code to do are;
- identify the task where a precedent starts
- identify the task where a precedent finishes

can you describe the logic rules for the above. And do those rules actually work for all data sets?

[ioncila]

Thank you very much for still supporting

I will try to explain the more clearer:

If you look at the last attached file, I have a "E" column called "ID" and a "G" column called "PREDEC". "ID" may be unnecessary if you consider row number itself as ID for each row (or task).

So, on "G" I shall indicate the ID of the of the taskbar (or row) where I want the precedent starts. The precedent finishes in the taskbar of the row wich has a value on the correspondent cell in "G".

Take the example on the attached, please:

"G22".value=3 - It means thet the precedent should start at row 21 (because value=3 is found in "E21") and should finish at "G22". So, the connector should connect existing bars in row 21 (start) and in row 22 (finish).

As the example shows, I may need to connect two or more tasks to the same task (see row 32). Again, another precedent should start at row 21 and finish at row 32, and so on.

I hope this make sense and logical.

Thank you so much for your interesting, time and help.

[ioncila]

Hi

I'm trying hard everyday to turn over my dificulties to go through this issue. Unfortunelly, there's something missing in my knowledge (something big, I guess) to step forward.

May this thread keep on? I don't know, as also I feel I'm pushing too much from the boys here.

Anyway, thank you for every help and support you gave me.

[Andy Pope]

Please Login or Register  to view this content.

[ioncila]

Thank you very much for your reply and the code you sent.
It shows almost the way I wish. However it works "inverted" and dependenciess don't match with the cells we want to.

As I belive the issue may be in "from" and "to" logic but, i tried to change it, but nothing happens.

I've attached the workbook where is indicated dependencies created by your code (in black) and in red (those I wish to appear).

So if you could take a look, it would be nice

Thanks

[Andy Pope]

The arrows are not correct because the bars are not correct.

Task one has a bar that is called Bar2

This change of the code will work in your example workbook.

Please Login or Register  to view this content.

[ioncila]

Thank you very very much. It works just fine, just how I imagine that.

Before I solve this, a last request please:

How do you remove existing connectors before update with new values? Here's what I was trying to do:

Please Login or Register  to view this content.

Thank you again

[Andy Pope]

Use the same approach as for the bars.
When creating the shapes given them a name that you can then use to remove them.

[ioncila]

What is wrong ?

Please Login or Register  to view this content.

[Andy Pope]

guessing...

Please Login or Register  to view this content.

But where in the code are you naming the newly added shape?
I would create another constant for naming the Connectors to avoid confusion with the bars.

[ioncila]

Sorry but it seems like I'm having a misunderstanding problem. I cant make a relation as you suggest

Please Login or Register  to view this content.

[Andy Pope]

I have added an argument to the build routine that will name the shape.

In the main routine the loop will delete all shapes with the right name prefix.

Please Login or Register  to view this content.

[ioncila]

Sorry for the insistance but there is an issue:

Code stops at

Please Login or Register  to view this content.

A msgbox with "Run time error '-2147024809(80070057)': Item with the spcefied name not Found"

[Andy Pope]

Do the bar shape names match what you are requesting in code.

Previously we had to add +1 because the bar names started at bar2.
If you have corrected that problem then it will fall over as the final reference will be to a bar that does not exist.

[ioncila]

I didnt correct anything because all seemed to work fine after your adding +1.

The only problem is to erase the existing connectors if I want to update with new values. When I do that the existing connectors remain.

An information that may be relevant:

When I run code for taskbars, it executes the update inspite of a msgbox "Run time error '1004':Impossible to obtain Match property of worksheetfunction class (my translation)"

This happens in the example workbook but works perfectly in my master file (error dont show up)

Andy: New edit:
Something strange. I erased manually all shapes and change values once and all code worked fine (except situation for taskbars). Then I changed values again and dependencies worked as wish (delete existing and print new connectors). I've done this a few times. So aparently it seems that is done and solved. I'm going to experiment in my master file and then I'll give you feedback.

[ioncila]

So I did experience for code in my master file:

1. Didn't work at once. I tried some changes to find out what was wrong and, after taking off that "+1" you added, it worked fine.

2. Then I merged code for bars and for connectors, all in one module. I began to have some troubles with the match function and with shapes properties. Sometimes it works, sometimes not. Once it shows an error type and then shows another

3. I added then "On Error Resume Next" on top of code and after that everything seems to work fine. I changed values and updated a dozen times and no more problems.

But I feel some instability. Did that "On Error Statement" solve those problems and make code run well from now on or do I need to look deeply?

[Andy Pope]

Indeed the On Error Resume is doing exactly what it says.

When the error occurs continune. So adding that line has not fixed the error it has simply masked it.

[ioncila]

Ok The error still there (masked, as you say). But the updates work well and with no calculating errors (with On Error Resume). How can it be? Will it work "trustly" like this from now on?

And, since both workbooks (example wb and master wb) have same structure, is there a logic explanation for different performances (example needs "+1" added and master not)?

Sorry if I put many questions.

Thanks

[Andy Pope]

I can not tell whether the code, with error checking, will work in all cases.

As I said the problem is caused by trying to reference a shape by name that does not exist. The root of your problem is that bars are/where created that are out of sequence with the task id.

[jokjok10]

Hi Sirs,

Good Day,

I've been spending some time to adapt my excel on the code provided by Andy and failing continuously. Appreciate if you could help me work with this please.cable pulling_edited_MACRO_1.xlsm