Macro to make linegraphs

[Anomandaris]

Hello again folks,

I'm trying to write a macro to make a line graph out of a set of data
Workbook contains 2 sheets - "DV-IVSpread" with data, it has 3 tables, Table 1 contains data that is represented in the grid on the other sheet "HeatMap", and Table 3 contains the data that I need to display in a line graph (but it will be 1 column at a time, e.g. graph of '1mx1y', graph of '3mx1y', etc....)
I will get back to this in a moment.

Now lets look at sheet"Heatmap", see the grid,
'1mx1y' on Table 1 in "DV-IVSpread" is shown in the grid, with '1m' on the Y axis(vertical) and '1y' on the X-Axis(horizontal).
So what I want is to click on the 1st square of the grid(cells C5:C7), and when I do that it makes a line graph for the data pertaining to 1mx1y on Table 3 (which has data for around 2 yrs). And I want that for all the 64 squares in the grid.


This means I need 2 things:
1. A code to make the line graph. There will only be one line graph displayed at a time (a small one abt half the size of grid), only a graph of the gridsquare that i select.
2. I need to be able to click on these grid squares...how do I do that, text boxes are not working..I need something like a transparent square box on top of each gridsquare so that I can still see the numbers and the colour, but at the same time be able to clic kon it to initiate the macro.

hope i was clear there
please advise, thanks

[mdmackillop]

Try this

[Anomandaris]

maybe i should change bit of what i said,
lets say that i make one line graph, but then depending on which grid square I click on 'Heatmap', the macro should refresh the linegraph

thats probably the best way to do it..
what say?

[mdmackillop]

Good idea!

[Anomandaris]

thanks mdmac, checking it out right now, seems good, wow you did it without having to throw in any messy boxes to attach the macro to.

[Anomandaris]

thanks mdmac, this is great, works like i wanted.

I'd like to add another option to this, to increase functionality
So far we've been charting one grid square at a time, what if i wanted to chart one row of grid squares, or one column of grid squares.
Outside the coloured heatmap, you can see the categories '1y,2y,5y, etc'
I want to click on 1y (which is cell C4) and get a graph of that column (8 grid boxes) so we would get data for 1mx1y,3mx1y,6mx1y, etc till 10yx1y. Data is in Table 3 Sheet'DV-IVspread' just like before, only now the line graph will have 8 lines.
this was vertical
also for horizontal---say I pick '9m' which is cell B15, I want the chart of the whole row, so data for 9mx1y, 9mx2y, and so on till 9mx30y....
oh and one thing in the line graph the x-axis is supposed to be time, the set of dates on 'DV-IVspread' in Column B,row 17 downwards.
It would be awesome if you could figure this one out, but even without that what you've done so far is very cool
thanks a lot mate

[Anomandaris]

I tried it out at work, its good...one thing though in the graph i need the x-axis to show the dates(from column B in 'DV-IVSpread')
how can i do that?

thanks

[mdmackillop]

Set up your chart as you would want it. The code just changes the data source.

[Anomandaris]

thanks mdmac, the chart works with the correct labels and everything.
Btw do you know how to do charts for horizontal row grid squares, and vertical column gridsquares? (the stuff i mentioned in post#6)

thanks

[mdmackillop]

t
Btw do you know how to do charts for horizontal row grid squares, and vertical column gridsquares? (the stuff i mentioned in post#6)
Not yet.

[mdmackillop]

I'll leave you to prettify it and work out labels etc.

[Anomandaris]

thanks a lot mdmac, but the code breaks in the following line:


Set Srce = .Rows("16:16").Find(arr(i - 1))


I have 2 other questions( this is regarding the previous graph that u did, on individual gridsquares):

1. The X-category values are disappearing from teh source data box for some reason....which is why when I refresh the graph all the dates disappear from the X-axis. How do i set it so X-axis values stay constant (range 'B16:B525' DV-IVSpread!).

2. The X-axis time values going right to left (because the dates are in descending order in DV-IVspread). I want it to be like a normal time series graph where we move left to right...is there a way to do this without changing the Dates descending order in Sheet 'DV-IVspread)?

thanks again buddy, you've helped me a lot with this, sorry abt the trouble

[mdmackillop]

No time to look at your questions, but this works for me in Excel 2000 (I had the same problem on a different PC)
Only problem is, if I use arrows to navigate, it makes Excel crash. Don't ask me why.:dunno

[Anomandaris]

thanks mdmac, dont worry abt it, it'll work for now.
i owe you bigtime!