Interactive Start Date with a Graph

Hi,
I need help getting started with a makro that I am not sure even exists. I would like to have a graph with interactive start and end points. This means I have data on economic growth and the user wants the graph to start in 2003 and end in 2009. In Sheet 1, I have the data beginning in 1970 and updating automatically from a separate server. In Sheet 2 I have a commandbutton that when pushed opens a new sheet with a graph from 1970 to 2010. I would instead want an input/message box to ask the user when they would like the graph to start and after they fill it in and press ok, the graph is created from 2003 to 2010. Can anyone help me with this? Any info would be highly appreciated.

Hallo in die Eifel!! <wink> (endlich mal jemand aus der Nähe der Heimat).

Attached is a sample dynamic chart for you to play with. Find all the principles of this explained here:
http://peltiertech.com/Excel/Charts/Dynamics.html

Tschö!

You don't need VBA to make the chart dynamic and interactive. It is simpler to opt for using a couple of input cells clearly identified as date inputs.

See this link for help on Creating Dynamic and Interactive Charts

Hello lmj4,

there's no need for the data to be on the same sheet as the chart, especially when you use range names to define the chart. In my previously uploaded file, the chart ranges are set dynamically, based on the values selected manually in two cells. You can of course use an InputBox to ask the user about the start and end date and then write that value into the fields. This will cause the dynamic ranges to recalculate, and you can then assign the range names to the chart series with code along the lines of:

Please Login or Register  to view this content.

If you need more help with this, you can post the workbook.

cheers

lmj4,

I'm no VBA buff, so what I can offer is only a suggestion.

Attached, I have created a few formulas and range names in the "Daten" Sheet.

There is a data validation box to ask for the start date.
There is a data validation box to ask for the end date.
There is a cell that calculates the number of rows to be included in the graph from the difference between start and end date.

I have defined a range name that takes the start date and the number of rows to calculate a dynamic range ( data1Range).

If I knew how to do it in VBA, I'd go this way:

create a form with a combo box, asking for the start date. The combo box values come from the populated date cells in the respective column.

On the same form, have a combo box asking for the end date. The combo box values are the same as the first combo box.

Calculate the row numbers between the "start" combo box value and the "end" combo box value. (find the location of the start date in the sheet, find the location of the end date in the sheet and subtract the location of the start date. add 1)

Now you have the parameters to define a range. Starting from Row 3, find the Start date and add the row numbers to define the total range to be included in the chart.

Write these values into variables and use these variables to construct the DrawString/DrawRange string.

I notice you have created a function to calculate column letters from numbers. You don't need column letters to address a cell. You can use the VBA Cells() property.

Cells(3, 1) is A3

The values in the brackets are Row, Column

Range(Cells(5, 3), Cells(10, 9)) is C5:I10

That may make your life easier when constructing the ranges.

I'm just taking my own baby steps in VBA and can't advise you on a whole solution, but maybe with these pointers, you can get a step closer to your solution.

cheers,

PS. Wenn's Dich mal nach Simmerath-Witzerath verschlägt, dann grüss meinen Bruder. Seine Pänz gehen/gingen in Monschau zur Schule.

If you use a dynamic range to define the chart series, then you only have to change the parameters for the dynamic range and the chart will update. You can use an InputBox or ComboBox to ask the user for start and end date. Store these values in a cell and let the dynamic range reference these cells. Have another look at my first attachment. The parameters for the dynamic range are defined in cell G3 (range name = start) and cell G5 (range name = span).

The dynamic range is defined with this formula for the labels

=OFFSET(Sheet1!$B$1:$B$42,MATCH(Sheet1!start,Sheet1!$B$2:$B$42,0),0,span,1)

and an offset from that range for the values. Whenever the values in G3 and/or G5 change, the range name will update, and so will the chart.

Hi Everyone,
I know the process I want to use to complete this project but I am still unfortunately having an awful time with the named ranges. My new question is that after inserting a new row before the data starts and then putting the start and end date in row a3 and b3, I want to change the VBA code Drawstring, so that it doesnt count the rows and instead will take the named ranges in a3 and b3. For the data in columns c and d, I will have a second named range and so on. Is there a way to make the charts take the ranges based on what is in a3 and b3, rather then the name of the range? Thank you.

I'm not sure I follow.

putting the start and end date in row a3 and b3

and

instead will take the named ranges in a3 and b3.

is contradictory.

Do A3 and B3 have dates or named ranges?

Sorry, you've lost me.

Can you produce a smaller sample file that illustrates the problem? I'm not too keen on downloading half a MB to get to the root of this.

Hi, okay, I took away the page with checkboxes. I have inserted 2 new rows before the data starts and then copied the named ranges, that were previously to the right of the data. I want to create named ranges for the data refering to columns c and d, as well as refering to e and f. That is no problem. However, once I have the named ranges, there is no way for Drawstring to recognise them without stating each Name. Therfore, I wanted Drawstring to recognise that a3 is Data1Range and then go on to data2 and recognise that the next name is c3 and then go to the data 3 and recognise that the name is e3. Is that clearer now?

Your code in the latest example will not run so it's hard to test.

This will plot the information in columns A:B based on the date selection in A3 and B3

CHT_DATE1: =OFFSET(Daten!$A$4,MATCH(Daten!$A$3,data1list,0),0,MATCH(Daten!$B$3,data1list,0)-MATCH(Daten!$A$3,data1list,0)+1,1)

CHT_DATA1: =OFFSET(CHT_DATE1,0,1)


Set the Category range to reference CHT_DATE1 and the Values to CHT_DATA1. So your series formula would look like
=SERIES(,'smaill_ excel_help.xls'!CHT_DATE1,'smaill_ excel_help.xls'!CHT_DATA1,1)


You can create another named range for data in column D.

CHT_DATA2: =OFFSET(CHT_DATE1,0,3)

For the information in columns E:F you will need to create a new named ranges

CHT_DATE3: =OFFSET(Daten!$E$4,MATCH(Daten!$A$3,Daten!$E$5:$E$29,0),0,MATCH(Daten!$B$3,Daten!$E$5:$E$29,0)-MATCH(Daten!$A$3,Daten!$E$5:$E$29,0)+1,1)
CHT_DATA3: =OFFSET(CHT_DATE3,0,1)

Thanks, Andy!

This will generate the correct Drawstring references.

Please Login or Register  to view this content.