Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink to a graph
I know this question has already been posted (and answered) in another thread
(http://www.microsoft.com/office/comm...=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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink to a graph
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 -- Gary''s Student - gsnu200855 "jellyroller" wrote: I know this question has already been posted (and answered) in another thread (http://www.microsoft.com/office/comm...=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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink to a graph
Two part solution:
First, to make the hand appear, change B2 to this formula structu =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!* "jellyroller" wrote: I know this question has already been posted (and answered) in another thread (http://www.microsoft.com/office/comm...=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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink to a graph
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 "Luke M" wrote: Two part solution: First, to make the hand appear, change B2 to this formula structu =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!* "jellyroller" wrote: I know this question has already been posted (and answered) in another thread (http://www.microsoft.com/office/comm...=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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink to a graph
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!* "jellyroller" wrote: 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 "Luke M" wrote: Two part solution: First, to make the hand appear, change B2 to this formula structu =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!* "jellyroller" wrote: I know this question has already been posted (and answered) in another thread (http://www.microsoft.com/office/comm...=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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink to a graph
I dont know why it wasnt working before but I updated it as you suggested and
that did the trick - thanks for your help "Luke M" wrote: 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!* "jellyroller" wrote: 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 "Luke M" wrote: Two part solution: First, to make the hand appear, change B2 to this formula structu =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!* "jellyroller" wrote: I know this question has already been posted (and answered) in another thread (http://www.microsoft.com/office/comm...=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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink to a graph. | Excel Discussion (Misc queries) | |||
Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
Hyperlinkage of one graph with another graph or Drill down graph | Charts and Charting in Excel | |||
How to create a hyperlink to a graph sheet | Charts and Charting in Excel | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) |