Macro to generate a line graph with coloured pointers and lables based on table

[B2009]

Hello,

Guys/ Ladies

I was wondering if I could leverage some expertise on creating a macro to achieve the following.

If we take the table below as an example...


column A = Date : 01/02, 07/02, 14/02, 21/02, 28/02 (x-axis : shows when the table is updated)
column B = project: x, x, x, x, ,x (name of the project and trend line)
column C = Delivery Date : 01/05/2009, 08/05/2009, 20/05/2009, 30/05/2009, 28/02/2009 (plotted on the graph)
column D = Status : Green, Amber, Red, Green, Blue (status of the project. the points should be the same colour as is described in the table)
column E = Comments: original, delay, supply, out of money, on track, delivered-wow! (these comments will pop up if the user holds the cursor over a point)

NB Y-axis scale : 01/01/2009 to 31/12/2009 with increments of 14 days. this will be the same scale used for all projects.

Date Project Delivery Date Status Comments
01/02 x 01/05/2009 Green original date
07/02 x 08/05/2009 Amber delay supply
14/02 x 20/05/2009 Red out of money
21/02 x 30/05/2009 Green on track
28/02 x 28/02/2009 Blue delivered-wow

So id like the macro to draw the line for project x based on the 'delivery date'. The points should be coloured according to the 'status' column and when you hover the mouse over the point the data lable will show up taking info from the 'comments' column.

Would it be possible to create a macro that will be able to generate this graph automatically. I have a few projects id like to do the same thing for.

Any help would be amazing!!!

Thanks in advance guys.

[Kafrin]

I'd highly recommend that you start by working out how to do all this manually, then recording a macro while you go through the process. This will give you most of the commands you need. Then repost if you need help adapting the code to a more general case.

That said, I'll have a quick look and see what I can come up with.


[edit]
OK, I've had a quick look and I'm not sure what graph it is you want out of the data. You said a line for Project x based on the Delivery Date. Based on the date how? What are your axes? To draw a graph you need x and y axes. I'd guess that your x-axis is the Delivery Date (so time moves forward as you go along the graph). What is your y-axis? what data are you plotting against each delivery date?
[/edit]

[B2009]

Thanks for your reply Kafrin - good question as i forgot to specify that.

The X-axis would be the first column 'date' (which is when the table is updated - in our case you'll notice that the table is updated everyweek during feb).

The Y-axis has to be 01/01/2009 to 31/12/2009 in increments of 14 days.

I can create the graph manually but realised that i can't just record the macro. This is because as you can see by the Y-axis, when i create the chart, ill have to manually tweak the scale.

I have already spent hours scouring the net to see if i can work it out but its a bit tricky :s

Thanks for taking a look

[Kafrin]

OK, that's making more sense. It'll take me some time to break down the code though. To be honest, I strongly recommend that you record the macro anyway and then post it up, as that'll considerably shorten the time it takes me.

You can keep the macro record going all the time while you're changing the axes and everything, but even if you don't it'll still help to have the basic chart-creation code as it'll help make sure you get the graph you're actually after!

For reference, the Y-axis minimum (1/1/09) is 39814 and the maximum (31/12/09) is 40179, major unit = 14. Once in code, these can be adapted to something that looks sensible, but for now htat'll allow you to set up the axis quickly.

[B2009]

Hi Kafrin,

I took your advice and here's the macro i managed to record:

Please Login or Register  to view this content.

I hope this helps

[Kafrin]

That does help a lot!

OK, so based on the table you gave in your first post, this is what I've come up with:

Please Login or Register  to view this content.

As you can see, I've given you a function and a sub. The function is to set the colours to use for the points, the sub is the code you want to run (this then calls the function).

The parts commented as year or project specific are things that could be adapted progamatically, but I haven't done this currently - it's probably best for you to get to grips with what's there and then look at adding in the niceties.

What I have not been able to do is give you the comments as the hover text for the points. Not sure how you do this (or if it's possible) - if you know how to do this manually then please let me know and I'll see if I can put the code together!

[B2009]

Kafrin thats really helped thanks

I can definitly tweak that to where I want it to be. There is a 'but' though. The issue is the way in which the data is collected. The table it updated with multiple projects per week - please see file attached.

I see that in the file we've had to reference the cells we want to target. I can tweek the columns but the rows might be an issue.

At the same time im scouring the net for ideas on how to make the data lables pop up when you hover over the points. It is possible but so far ive only seen it work if you install a plug-in.

Thanks again for your time and efforts

[Kafrin]

It's definitely something that can be automated, I'll work on it!

[DonkeyOte]

B2009, please note re: post # 5

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please amend the prior post accordingly.

All, please do not make further posts until the above is actioned.

Regards,
D.O.

[Kafrin]

I've found the same advice on the web about changing the hover text - seems it's not in-built but people have developed add-ins.

As for the changes to make this work for your (variable) tables, try replacing all the code I gave before with this:

Please Login or Register  to view this content.

As long as your columns are consistently called Date and Go Live Date (and these words don't make up the entire contents of another cell on the sheet) this will now work. In fact it works on either of the two sheets in the file you uploaded as it doens't matter where the headings are; it finds the heading, then takes from the cell below it all the way to the bottom of the data.

[DonkeyOte]

Given Kafrin has been kind enough to post further so as to assist you coupled with you "newbie" status on the board I have on this occasion edited your post for you applying CODE tags as required.
Please endeavour to read and abide by the Forum Rules going forward.
Thanks in advance.

[B2009]

Hi Kafrin - you are a genius!! Thank you so much. Please feel free to contact me on [removed by Mod] if I can ever be of any help

DonkeyOte - Appologies if I broke the rules at all. I'll make sure to read the forum rules.

Thanks again to you both

[DonkeyOte]

B2009, I have removed your mail address, unless you intend to be spammed to oblivion I would advise leaving it out of your posts ... by all means send Kafrin a PM with your email address if you so wish.

[B2009]

BINGO!!!! Kafrin I found it a code that allows you to show the labels on the chart once you hover over a pointer. I tested it out on a new set of data and so far it works.

Please Login or Register  to view this content.

code taken from : http://www.databison.com/index.php/i...se-move-event/

[Kafrin]

Ooh, excellent. I'll have to have a proper play with that code, looks interesting.

Glad the rest of the code I supplied worked :-)

[B2009]

Hi Kafrin

Just two queries with the code.
a) the colours dont seem to show - just red
b) would it be possible to refrence the project name as the chart title and name of the tab when the macro is initialised?

I tried to do the refrencing myself with the code you have written for the dates but it keeps throwing up errors.

:s

[Kafrin]

The colours. make sure you're using the updated version of the function:

Please Login or Register  to view this content.

Using "Select Case" simplifies some aspects of the coding (compared to using an If statement), but I only recently discovered that it is case-sensitive (I'm usually using numbers). Being text as well, it won't work if the colours are mispelled at all.

I'll take a look at the title now and get back to you.

[Kafrin]

Try this (to replace your exisiting code as I had to add bits in several places):

Please Login or Register  to view this content.

Note that this only works for the sheet you had that had info for a single project on it, not for one with multiple projects.

[B2009]

[Solved] Great work Kafrin Really helped out here. [/Solved]

[DonkeyOte]

Please Login or Register  to view this content.

Using "Select Case" simplifies some aspects of the coding (compared to using an If statement), but I only recently discovered that it is case-sensitive (I'm usually using numbers)

Just to elaborate on above... VBA in general is case sensitive (binary comparison) unlike native XL ... you can adjust for this either via use of Option Compare Text at the head of a Module or if preferred by coercing strings being compared to a common case

Using the above function as an example:

Please Login or Register  to view this content.

As was pointed out to me today certain functions do have optional parameters to stipulate case sensitivity (eg InStr and vbTextCompare)

[Kafrin]

Thanks DonkeyOte, useful to know. I do like your UCase solution - simple and effective.

I work more often in Access than Excel, and I rarely have a problem with case-sensitivity (although admittedly the Select Case statement is not something I use a great deal, so maybe it just hasn't been issue for me when I've used it). If you have some thing like:

Please Login or Register  to view this content.

then it is not case-sensitive, which is why it caught me out in this situation. I suspect this is to do with the default for modules in Access to have an Option Compare statement at the top, something I tend to take for granted.