Thread: Creating chart, conditional on cell clicked

Hi there,

Well, you were right - it was a bit complicated - not too difficult to program, but not all that easy to understand!

Please take a look at the attached and let me know if I've understood your requirements correctly. The only thing the user has to do is to enter the required dates and then select the required values of Term and Product from the dropdown lists. The graphs will update automatically.

I'd be glad to have your feedback on this one, even if it's only to let me know that I've completely misunderstood what you had in mind!

Regards,

Greg M

sorry, i know its not easy to understand as i've got 'Years/ time' on both axes...but you guys got it right.
Excellent work, both of you Greg and Andy! Looks like you guys put some time in for this, definitely does the job and looks organised.
But do you know by any chance if the dates could be selected with a scrollbar, just that I was asked to do it with scrollbars for ease of use.

thanks a lot guys...both versions work

Hi again,

I originally thought about using dropdown lists for the dates, and doing this will avoid the possibility of entering a date for which no data are available. However, the possible disadvantage is that users may have to scroll down through several hundred dates on the list, and this might prove to be more trouble than it's worth.

It's not a problem to make the dates available from a dropdown list, but are you sure that's the best approach?

Incidentally, if you're interested, I produced a slightly modified version of the workbook which would allow you to change the layout of your main data worksheet, i.e. to have more/fewer columns before the date column, and more/fewer rows before the title column.

Please let me know if you want me to implement the date dropdown list or anything else.

Regards,

Greg M

thanks guys, i'll just try plugging in a scrollbar, i've tried before and failed i'll give it another shot

Hi again,

Take a look at the attached workbook and see if the scrollbar does what you want.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Sorry - try this version instead! It will cope with up to 1000 dates.

Please let me know how you get on.

Regards,

Greg M

thanks Greg, the scroll bar does work like i wanted, now I'll try plugging it into my sheet, hopefully i can make it work there like you did, i'm surprised there was no vba required at all for the whole charting process

Hi again,

Thanks for your feedback - I'm glad I was able to help.

Ok on not needing VBA for the chart updating. I usually find that it's easier to "point" a chart at a specific range and to manipulate the contents of that range, than to try to manipulate the chart itself. As a consequence, the amount of VBA required is often very little, and relates just to updating one or two cells whose values are then used to populate the data range .

Regards,

Greg M

I'll mark this solved now, sorry about the delay was travelling last few days.
You guys did a fantastic job hee, thanks....though I just cant get my scrollbar to work, I'm making some very basic mistake I'm sure, but that's ok. If I run in to any problems while making additions to the model I'll let you know

thanks again

Hi Greg,

here's the scrollbar issue, when I tried to link it to the cell 'ptrScrollOffset1' the scrollbar disappeared...

Oh and another thing, about labelling the chart -- on the current version for Term 1y, see that on the X-axis we have 1y in 1y, 1y in 2y, 1y in 5y etc... is there a way to make that 1y,2y,5y, and so on till 30y on the X-axis, and at the top of the chart it'll say Term 1y.....

something like that, would make it look more concise, whats the formula for that, do i type it into Chart Title after selecting chart options?


thanks again buddy

Hi again,

Ok - I think things started to go wrong for you when you decided to use the Activex scrollbar (from the Control Toolbox toolbar) rather than the one you find on the Forms toolbar. Activex controls are more powerful, but also less user-friendly.

I've adjusted the x-axis titles on the attached version and hope that I've got them the way you wanted.

Hope this helps - please let me know how you get on.

Regards,

Greg M

thanks Greg..

i didnt know about the forms scrollbar before, it is way easier for sure, but
something's wrong I'm getting dates from Jan 2000 to Sep 2002 which is not even in my range so its giving me a blank graph. Am I supposed to hihlight the Dates Range in 'Data' while making thescrollbar?