Button in Excel to go from one Chart to another 1

[jvanderw]

I have an excel spreadsheet with about 40 charts in it (as individual tabs). I am going to publish this spreadsheet to the web and want to have a button on each chart that will go to the next chart. For example, if chart 1 is called Data1, chart 2 is Data2 and chart 3 is Data3, then on Data2 there would be a button to go to Data1 and a button to go to Data2. I thought that a hyperlink could be done, but they can only reference cells on a tab and charts don't have any cells to reference. Can anyone help me out?

Also, if you know of a better piece of software that can be used to build data graphs, link them together (as described above), publish them to the web, and include a table of contents please let me know (and reasonably priced, $500 or less). I am in the early stages of a rather large project, and want to use the most efficient software as possible.

Thanks!!!

 

[SkipVought]

Hi,

You can for a Forms Button on each sheet.

Each Button assigned to the SAME macro...

Sub NextChart()
    i = ActiveChart.Index
    If i = Charts.Count Then
        i = 1
    Else
        i = i + 1
    End If
    Charts(i).Activate
End Sub

then you could have one for previous...

Sub PrevChart()
    i = ActiveChart.Index
    If i = 1 Then
        i = Charts.Count 
    Else
        i = i - 1
    End If
    Charts(i).Activate
End Sub

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jvanderw]

Skip,
This works some of the time, my problem is that the charts are not in the same order that they were created. By this I mean that I may have made the charts in order 1, 2, 3 and the decided that it would be better to have them in the order of 1, 3, 2. Thus, using the code that you supplied I would (using the "forward" arrow) go from 1 to 2 to 3 when I really want to go from 1 to 3 to 2.
Do you have any other ideas to help me with this portion of the problem?
Thanks a bunch for the help!!
Josh

 

[SkipVought]

well then you could make a list with the chart sequence, name it, for instance "ChartSeq"

then

Sub NextChart()
    i = ActiveChart.Index
    i = Application.Match(i, Range("ChartSeq"), 0)
    If i = Range("ChartSeq")(Range("ChartSeq").Cells.Count) Then
        i = Range("ChartSeq")(1)
    Else
        i = Range("ChartSeq")(i + 1)
    End If
    Charts(i).Activate
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com