Excel Tips from Xlteq - Using Hyerlinks to Run Code
There are two ways you can run VBA code using Hyperlinks in Excel, either by clicking on the hyperlink or moving the mouse over the link.
Using Hyperlinks to Run Code
Clicking on the hyperlink
To run some code when clicking on a hyperlink, the following steps should be taken.
1. Type in the text you want to see in the cell you want it to appear in.
2. Then right click on the cell and choose the options “Link” and then “Insert Link…”
3. A window will open:
- Select the “Place in this Document” option on the left hand side
- Select the sheet name you placed the text in (in this example it was “Sheet1”)
- Clear the value shown in the “Type in the cell reference” box (it will default to “A1”).
- Click “OK”. The text may change colour and appear underlined when you do this.
4. Enter Visual Basic and double click on the sheet in the Project Explorer to bring up the sheet code
5. Select “Worksheet” and “FollowHyperlink” from the dropdowns to create the Sub.
6. Then enter the following code
If Target.Range.Address = "B2" Then Call YourCodeToRun
Where “B2” refers to the cell you entered the text on the sheet and “YourCodeToRun” is the name of the sub you want to run
Mouse over a hyperlink
You can also call code when you move your mouse over a hyperlink.
1. Create a Function (not a sub) which has the code you want to run (the function is called Test() in the following example)- Public Function test() As Boolean
- Sheet1.Range("A1") = Rnd()
- End Function
2. Enter the following into the cell you want to have the hyperlink in
- =IFERROR(HYPERLINK(test(),"Move over to run"),"Move over to run")
4. The macro will run for as long as the mouse is over the hyperlink
For a free consultation
Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.