Link To Charts

[Guest]

Hi,

i have an excel workbook with quite a few charts in it. All the data for the
charts is stored on one sheet, is there a way of creating a hyperlink for
each set of data to the relevent chart? I dont fancy doing buttons for every
piece of data (that would work out at well over 30 buttons!)

 

[Andy Pope]

Hi,

See Jon's page for details of hyperlinks.
http://peltiertech.com/Excel/Hyperlinks.html

Cheers
Andy

 

[Rich]

That worked great. How would you alter the macro for multiple sheets in the
same excel file? For example, cell b2 will take you to one worksheet, b4
will take you to another worksheet, etc.

Thanks,
Richard

 

[Andy Pope]

Hi,

Depending on the number of links you just need to duplicate the code
changing the test range.

If you have many cells to test then maybe,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rngTestTarget As Range

For Each rngTestTarget In Range("B2,B4,B6").Cells
If Not Intersect(Target, rngTestTarget) Is Nothing Then
On Error Resume Next
Charts(Target.Value).Activate
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, _
"Chart Not Found"
End If
On Error GoTo 0
Exit For
End If
Next

End Sub

Cheers
Andy

 

[Rich]

I found another way to do it. I put buttons in with the following code
behind each of the two buttons. The first one moves over one sheet the
second moves over two:

Sub Macro2()
'
' Macro2 Macro
ActiveSheet.Next.Select
End Sub

Sub Macro3()
' Macro3 Macro
ActiveSheet.Next.Select
ActiveSheet.Next.Select
End Sub

 

[Doug]

I tried doing this and it just says that no such chart exists.
I pasted the formula into the view code and when I select the cell B2 in the
worksheet and that is all that happens. Any suggestions? I know I must be
doing something wrong?

 

[Andy Pope]

The error suggests that the contents of B2 does not contain the chart sheet
name or that it is spelt incorrectly.

Cheers
Andy

 

[Doug]

I have multiple charts in several sheets. I checked to make sure the chart
names match the names I have in the range B2 through B12 of my headings on
the data sheet.
Any more suggestions?

 

[Andy Pope]

That code example is for hyperlinking to chart sheets.
Your description of "multiple charts in several sheets" would suggest you
have chartobjects on worksheets.

If that's the case then you can use the HYPERLINK formula to link to a cell
near the chart object.

Cheers
Andy