Hyperlinks That Run VBA

DID YOU KNOW?…

that you can create a hyperlink from an object to a VBA procedure. In
Excel 97, make a text box on a worksheet and right-click its edge. Then,
select Insert, Hyperlink from the menu and type the name of the procedure
in the Named Location in File box.

NOTE: The manual setting of a procedure name as a hyperlink subaddress
apparently cannot be done in Excel 2000. Not only does the dialog box not
allow that option, it does not allow the use of names that do not yet exist,
unlike Excel 97. In my opinion, there was no reason to add an extra
validation step to see if the name exists, since the option to add the name
at a later time increases the flexibility of this feature. However, you can
still set/change the SubAddress of the hyperlink programatically in Excel
2000 for VBA procedures.

Posted in Uncategorized

9 thoughts on “Hyperlinks That Run VBA

  1. You can also right click on a shape and select Assign Macro. This pops up a list of available macros in all open workbooks. This is also available programmatically:

    ActiveSheet.Shapes(“Rectangle 1?).OnAction = “MyMacroName”

  2. Here’s a bunch of things that one can do with hyperlinks. May be old news to some but I doubt to all.

    First, as already indicated, in XL97 one can enter the name of a subroutine through the UI in the ‘sub address’ or bookmark field. In XL2000+ it has to be done programmatically with code like
    activesheet.hyperlinks.add activesheet.shapes(1),thisworkbook.Name,”XYZ”
    for a shape, or
    activesheet.hyperlinks.add range(“c6?),thisworkbook.Name,”XYZ”
    for a cell.

    With the “how to” out of the way…

    Clicking on the link will take one to the subroutine in the VBE.

    However, adding parenthesis after the subroutine name will cause the subroutine to be called *twice* followed by a ‘Reference is not valid’ error.

    Another way to do leverage hyperlinks is through the FollowHyperlink event procedure. This is available only in XL2000 or later. With very little code — and code that needs *no* active maintenance! — this can be very effective.

    Establish a convention that the text of the hyperlink is the name of the procedure to call. For visual (non)effect, the hyperlink address should be the workbook itself and the subaddress (i.e., the bookmark) should be the address of the cell containing the hyperlink.

    Now, in the FollowHyperlink procedure all one needs is
    Application.Run Target.TextToDisplay

    One can even take this a step further. The foll. code either runs a subroutine or selects a chart.

    On Error Resume Next
    Application.Run Target.TextToDisplay
    If Err.Number 0 Then
    On Error GoTo 0
    Target.Parent.Parent _
    .ChartObjects(Target.TextToDisplay).Activate
    End If

  3. If your aim is to take one to the subroutine in the VBE, then you can following:

    In Excel 97:
    Insert > Hyperlink: In “Named location in file (optional):” put the name of
    the procedure.
    So if the procedure name is MyProc, you would enter “MyProc”.

    In Excel 2000:
    Insert > Hyperlink: Click on “Existing File or Web Page”, then in “Type the
    file or Web-page name” put the name of a procedure preceeded by a hash (#).
    So if the procedure name is MyProc, you would enter “#MyProc”.

    In Excel 2002 (XP):
    Insert > Hyperlink: Click on “Existing File or Web Page”, then in “Address”
    put the name of a procedure preceeded by a hash (#).
    So if the procedure name is MyProc, you would enter “#MyProc”.

    Jiri Cihar
    http://www.dataspectrum.cz/pages/eng_files/engfilesmain.htm

  4. Ross said

    “what happen to a 97 wsk with #MyProc, doe it fail?”

    According my experience, in XL97 using hash (#) fails.
    But
    1) I had used nonenglish version (Czech to be quite precise)
    2) I recall that using hyperlinks in Czech version of XL97 was a bit of unstable and unpredictable.

    Jiri Cihar

  5. The “#MyProc” suggestion in the address link for inserting a hyperlink does not execute the code within the procedure but merely opens the VBE and shows you the code. How do you tell the hyperlink to execute the procedure? Thanks!

  6. When I do this #MyProc() in Excel 2007, the sub runs twice and then I get a “reference not valid” msg.

  7. Juan said:

    “When I do this #MyProc() in Excel 2007, the sub runs twice and then I get a “reference not valid” msg.”

    I get the same “reference not valid” msg. I need to execute the procedure upon clicking the hyperlink, and pass the value of the cell to the procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *