Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
OK. I looked this up and got the answer I was looking for (see code below)
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) TmpRef = Chr(ActiveCell.Column + 64) & ActiveCell.Row If Not Intersect(Target.Parent, Range(TmpRef)) Is Nothing Then Call Unhide_Sheet End If End Sub This all works fine and I have now started using the functionality when building spreadsheets. My problem comes when users of Excel 97 (I use 2000) take the same spreadsheets and try to run the hyperlinks. The macro does not work. I this because the code is not supported in Excel 97? Please help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
Hi Rich,
Yes, the problem is the Worksheet_FollowHyperlink event was first added in Excel 2000. Excel 97 won't recognize it as an event procedure, so it will not fire when your project is run in Excel 97. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Rich" wrote in message ... OK. I looked this up and got the answer I was looking for (see code below) Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) TmpRef = Chr(ActiveCell.Column + 64) & ActiveCell.Row If Not Intersect(Target.Parent, Range(TmpRef)) Is Nothing Then Call Unhide_Sheet End If End Sub This all works fine and I have now started using the functionality when building spreadsheets. My problem comes when users of Excel 97 (I use 2000) take the same spreadsheets and try to run the hyperlinks. The macro does not work. I this because the code is not supported in Excel 97? Please help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
Hi Rob,
I have tried the above code. There is a little problem. When clicked the hyperlink in the sheet, excel follows the link, opens the linked document and then runs the macro. Is it possible to stop opening the linked document and only run the macro? Also is it possible to run the macro by typing the name in a cell (like =runmacro("hide_sht") For this I Googled a lot without success. Regards, Shetty. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
Hi Shetty,
I have tried the above code. There is a little problem. When clicked the hyperlink in the sheet, excel follows the link, opens the linked document and then runs the macro. Is it possible to stop opening the linked document and only run the macro? You can do this by using a dummy hyperlink. This is a hyperlink that points to the same cell it is located in. When the hyperlink is clicked the only thing that happens in the Excel user interface is that the cell containing the hyperlink is selected. However, the Worksheet_FollowHyperlink event fires and you can do everything you want to do after the hyperlink is clicked using VBA. Also is it possible to run the macro by typing the name in a cell (like =runmacro("hide_sht") For this I Googled a lot without success. You could use the Worksheet_Change event to watch a specific cell and run the macro whose name was entered in that cell each time it changes. Let's say you wanted to run any macro whose name was entered in cell A1. The event procedure would look like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Application.Run Target.Value End If End Sub -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm "Shetty" wrote in message oups.com... Hi Rob, I have tried the above code. There is a little problem. When clicked the hyperlink in the sheet, excel follows the link, opens the linked document and then runs the macro. Is it possible to stop opening the linked document and only run the macro? Also is it possible to run the macro by typing the name in a cell (like =runmacro("hide_sht") For this I Googled a lot without success. Regards, Shetty. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
Well....
Thanks Rob. But this can be done only for some predefined cells. Can it be done for any cell (not predefined) or without Worksheet_Change event ? Just a curiosity. Thanks for reply. Regards, Shetty |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
Well....
Thanks Rob. But this can be done only for some predefined cells. Can it be done for any cell (not predefined) or without Worksheet_Change event ? Just a curiosity. Thanks for reply. Regards, Shetty |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a Macro from a Hyperlink
Hi Shetty,
<<But this can be done only for some predefined cells. Can it be done for any cell (not predefined) If you wanted to do it for any cell on the worksheet, you could use On Error Resume Next to blow by anything that wasn't a valid macro name. Not my preferred way of programming, but it would work: Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Application.Run Target.Value End Sub <<or without Worksheet_Change event ? You could set the legacy Worksheet.OnEntry property to a macro that did the same thing, but that isn't conceptually different from using the Worksheet_Change event. There aren't any other ways that I can think of right off the bat. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
Macro to Copy Hyperlink to another file as a HYPERLINK, not text... | Excel Programming | |||
Running a macro from a hyperlink | Excel Programming | |||
running a VBA sub from a hyperlink | Excel Programming | |||
Launch Macro in Access via Macro running in Excel??? | Excel Programming |