I’ve seen shapes placed on Excel sheets used to activate Excel VBA. A more elegant solution is to use text hyperlinks. Let’s say your Excel App has three actions:
- Cleanup Downloads Folder
- Save Output as PDF
- Draft Email
Here’s how to activate these actions using hyperlinks:
- In your Excel App, type your actions.
- Create a hyperlink for each. The destination for each hyperlink should be a “Place in This Document”, directly to the cell you’re adding the hyperlink to. That’s so when you click the hyperlink, you’re not taken elsewhere.
- 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 clicking on a hyperlink on that sheet.
- Select Case Target.TextToDisplay identifies the text of the clicked hyperlink.
- If the hyperlink text is Cleanup Download Folder, the script under/next to Case Is = “Cleanup Download Folder” will run. In this case there’s only a single line next to it that’s calling Module1.CleanupDownloadFolder.
- If the hyperlink clicked on has a text of Save Output as PDF, then the script under/next to Case Is = “Save Output As PDF” will run. In this case there’s only a single line next to it 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.