Hyperlink to a graph

[jellyroller]

I know this question has already been posted (and answered) in another thread
(http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1)
but I was hoping to get some clarification / help with the macro that John
Peltier offers.

I have used this macro and it works well but there are a couple of problems
with it (from my perspective). Firstly every time I go to the cell (i.e. not
just when clicking on the cell) then Excel goes to the graph - is there
anyway to update the macro to only go when clicked on?
Is it possible to make the cursor change to the hand icon (i.e. showing it
like a hyperlink) when the cursor hovers over the cell that it hyperlinks
from?

 

[Gary''s Student]

You can use Peltier's solution with an indirect hook. Say in B1 we insert a
REAL hyperlink to a place in the document - some remote place like Z1000. We
can assign any "friendly name" to this hyperlink and also give it any
appropriate popup message for the mouse-over.

Now in Z1000 we put the chart name and mod Peltier's code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("Z1000")) 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
End If
End Sub

 

[Luke M]

Two part solution:
First, to make the hand appear, change B2 to this formula structure:
=HYPERLINK("[Book1]Sheet1!B2", "Chart1")
Where
Book1 = name of workbook
Sheet1!B2 = cell address that you just put the formula in
"Chart1" is name of chart tab to go to

Modifying Jon's macro slightly, we can make it so that it only activates
when you double-click on the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("B2")) 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
End If
End Sub

 

[jellyroller]

Luke,

Thanks for taking the time to help me out - this is certainly getting me
closer to it. I did what you suggest but everytime I try to click on the cell
I get "Cannot open the specified file" or when I tweak around it tells me
that I have created a circular reference and it just sets the cell to 0.

Any ideas?

Thanks

Two part solution:
First, to make the hand appear, change B2 to this formula structure:
=HYPERLINK("[Book1]Sheet1!B2", "Chart1")
Where
Book1 = name of workbook
Sheet1!B2 = cell address that you just put the formula in
"Chart1" is name of chart tab to go to

Modifying Jon's macro slightly, we can make it so that it only activates
when you double-click on the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("B2")) 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
End If
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I know this question has already been posted (and answered) in another thread
(http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1)
but I was hoping to get some clarification / help with the macro that John
Peltier offers.

I have used this macro and it works well but there are a couple of problems
with it (from my perspective). Firstly every time I go to the cell (i.e. not
just when clicking on the cell) then Excel goes to the graph - is there
anyway to update the macro to only go when clicked on?
Is it possible to make the cursor change to the hand icon (i.e. showing it
like a hyperlink) when the cursor hovers over the cell that it hyperlinks
from?

 

[Luke M]

The easier route may be to have XL setup the hyperlink. Get rid of formula,
and just type name of Chart tab. Now, go to Insert - Hyperlink. Along the
left side of box, click "Place in this document". For the cell reference,
type the address of the cell you are editing (in example, B2). Then click ok.

What happens is the link should just feed back on itself, so the screen
won't change when you single click on it, but the double-click activates the
macro, sending you to the graph.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Luke,

Thanks for taking the time to help me out - this is certainly getting me
closer to it. I did what you suggest but everytime I try to click on the cell
I get "Cannot open the specified file" or when I tweak around it tells me
that I have created a circular reference and it just sets the cell to 0.

Any ideas?

Thanks

Two part solution:
First, to make the hand appear, change B2 to this formula structure:
=HYPERLINK("[Book1]Sheet1!B2", "Chart1")
Where
Book1 = name of workbook
Sheet1!B2 = cell address that you just put the formula in
"Chart1" is name of chart tab to go to

Modifying Jon's macro slightly, we can make it so that it only activates
when you double-click on the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("B2")) 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
End If
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I know this question has already been posted (and answered) in another thread
(http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1)
but I was hoping to get some clarification / help with the macro that John
Peltier offers.

I have used this macro and it works well but there are a couple of problems
with it (from my perspective). Firstly every time I go to the cell (i.e. not
just when clicking on the cell) then Excel goes to the graph - is there
anyway to update the macro to only go when clicked on?
Is it possible to make the cursor change to the hand icon (i.e. showing it
like a hyperlink) when the cursor hovers over the cell that it hyperlinks
from?

 

[jellyroller]

I dont know why it wasnt working before but I updated it as you suggested and
that did the trick - thanks for your help

The easier route may be to have XL setup the hyperlink. Get rid of formula,
and just type name of Chart tab. Now, go to Insert - Hyperlink. Along the
left side of box, click "Place in this document". For the cell reference,
type the address of the cell you are editing (in example, B2). Then click ok.

What happens is the link should just feed back on itself, so the screen
won't change when you single click on it, but the double-click activates the
macro, sending you to the graph.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

Luke,

Thanks for taking the time to help me out - this is certainly getting me
closer to it. I did what you suggest but everytime I try to click on the cell
I get "Cannot open the specified file" or when I tweak around it tells me
that I have created a circular reference and it just sets the cell to 0.

Any ideas?

Thanks

Two part solution:
First, to make the hand appear, change B2 to this formula structure:
=HYPERLINK("[Book1]Sheet1!B2", "Chart1")
Where
Book1 = name of workbook
Sheet1!B2 = cell address that you just put the formula in
"Chart1" is name of chart tab to go to

Modifying Jon's macro slightly, we can make it so that it only activates
when you double-click on the cell.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Not Intersect(Target, Range("B2")) 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
End If
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


:

I know this question has already been posted (and answered) in another thread
(http://www.microsoft.com/office/com...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1)
but I was hoping to get some clarification / help with the macro that John
Peltier offers.

I have used this macro and it works well but there are a couple of problems
with it (from my perspective). Firstly every time I go to the cell (i.e. not
just when clicking on the cell) then Excel goes to the graph - is there
anyway to update the macro to only go when clicked on?
Is it possible to make the cursor change to the hand icon (i.e. showing it
like a hyperlink) when the cursor hovers over the cell that it hyperlinks
from?