Activate Excel VBA Using Hyperlink

I’ve frequently seen shapes placed on Excel sheets used to activate Excel VBA. A more elegant solution is to use text hyperlinks.

For example’s sake, let’s say your Excel App has three actions you regularly utilize:

  • Cleanup Downloads Folder
  • Save Output as PDF
  • Draft Email

Here’s how to activate these actions using hyperlinks:

  1. On your Excel App menu sheet, type your actions.
  2. Create a hyperlink for each. The destination for each hyperlink should be a “Place in This Document”, to the cell you’re adding the hyperlink to. That’s so when you click the hyperlink, you’re not taken elsewhere in your Excel App.
  3. In the VBA Editor, go to the Microsoft Excel Object corresponding to the sheet the hyperlinks are on. Add the following:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Select Case Target.TextToDisplay
        
        Case Is = "Cleanup Download Folder"
            Module1.CleanupDownloadFolder
        
        Case Is = "Save Output as PDF"
            Module1.SaveOutputAsPDF
        
        Case Is = "Draft Email"
            Module1.DraftEmail
    
    End Select

End Sub

Here is what the VBA is doing:

  • VBA entered into the “Microsoft Excel Object corresponding to the sheet the hyperlinks are on” interact with that sheet, but can call other modules.
  • Worksheet_FollowHyperlink intercepts the act of clicking on a hyperlink on that sheet.
  • Select Case Target.TextToDisplay identifies the text of the clicked hyperlink.
  • If the hyperlink clicked on has a text of Cleanup Download Folder, then the script under Case Is = “Cleanup Download Folder” will run. In this case there’s only a single line underneath that’s calling Module1.CleanupDownloadFolder.
  • If the hyperlink clicked on has a text of Save Output as PDF, then the script under Case Is = “Save Output As PDF” will run. In this case there’s only a single line underneath that’s calling Module1.SaceOutputAsPDF.
  • …etc… for each “Case Is”-statement until you get to End Select.

You will need to adapt Worksheet_FollowHyperlink to your own Excel App.

Scroll to Top