Sneak a quick peek at your worksheet’s hyperlinks — and their details
by Kara Soos
Application:
Microsoft Excel 2000/2002/2003/2007
Operating System:
Microsoft Windows
Hyperlinks are a great way to access a relevant website right from within Excel — or jump to a different place in your workbook without digging through all your data. But if you use hyperlinks generously, you may have trouble hunting them down when you want to change a destination or fix a broken link. Our macro lists every hyperlink in your worksheet — along with its destination, displayed text and ScreenTip — so you don’t need to waste valuable time sniffing out the information on your own.
To list all of our worksheet’s hyperlinks and their details, we’ll:
• Create a sample workbook with both external hyperlinks and internal hyperlinks (those that take you to different sections of the workbook).
• Produce a user-defined function that lists a cell’s hyperlink information.
• Build a macro that cycles through your worksheet’s data and applies the user-defined function to any cell with a hyperlink, displaying the resulting list in a message box.
If you stick with Excel’s default formatting, it’s probably easy to pick out your worksheet’s hyperlinks. Their text is usually blue and underlined (or purple and underlined if you’ve clicked on it once already). But when you have dozens of them in one worksheet, why scroll around and click each one to figure out where they all go? Instead, our macro gives you a list of all the hyperlink information you could ask for in seconds, as shown in Figure A.
A:
You can see all of the worksheet’s hyperlinks, along with their information, in our macro’s message box.
Add hyperlinks to your worksheet
Let’s take a look at a sample worksheet that will benefit from both external hyperlinks and internal hyperlinks.
Connect to a website
The Overview worksheet shown in Figure B includes two website addresses — obvious candidates for a hyperlink. We’ll start by turning those URLs into hyperlinks that will open the website in an Internet browser when you click on the cell.
B:
Cells B4 and B14 include website addresses that you can transform into actual hyperlinks.
To insert an external hyperlink:
1. Launch Excel and open our sample workbook, or create one based on the data shown in Figure B.
2. Select cell B4 and press [Ctrl]K to open the Insert Hyperlink dialog box.
3. Click the Existing File Or Web Page icon in the Link To panel.
4. Enter www.eliresearch.com in the Address text box.
5. Replace the text in the Text To Display text box if you wish. By default, Excel fills in the cell’s existing text, which is the website address in our example.
6. Click the ScreenTip button to open the Set Hyperlink ScreenTip window.
7. Enter Eli Research home page, as shown in Figure C, and then click OK.
8. Now that the Insert Hyperlink dialog box looks similar to the one in Figure D, click OK to create the new hyperlink, along with its display text and ScreenTip.
When you hover your mouse pointer over cell B4, you’ll see the familiar pointing finger icon that signals a hyperlink. In addition, the cell’s text is blue and underlined, just as a link on a web page would look. As you hover over the cell, your ScreenTip displays as well.
Repeat these steps: We followed these steps to create another hyperlink in cell B14 to the Eli Journals home page at www.elijournals.com.
C:
A ScreenTip is a message that displays when you hover your mouse pointer over a hyperlink.
D:
We want to display the website’s actual address, but you can display a description instead by changing the text in the Text To Display text box.
Jump to another location in your workbook
You can also use hyperlinks to move quickly to relevant data within your workbook. For insta
