Running a Macro from a Hyperlink

[Guest]

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!

 

[Rob Bovey]

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

 

[Shetty]

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.

 

[Rob Bovey]

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]

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

 

[Shetty]

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

 

[Rob Bovey]

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