Hyperlinks in Excel
A hyperlink is a convenient way to allow the user of a workbook to instantly access another place in the workbook, another workbook, or a file associated with another application. A hyperlink can be inserted in a cell or a shape in Excel. Select the cell or shape and select Hyperlink from the Insert menu, or right click on the cell or shape and select Hyperlink from the pop up menu. You can enter a cell reference in the current workbook, browse to another workbook, a different file, or a web page, even enter an email address and subject line. You can also edit the hyperlink text for a hyperlink in a cell.
Hyperlink to a Worksheet Range
This is almost a trivial exercise. Enter the cell reference, or select a defined name. If you browse to another workbook, Excel shows an Address field in the Insert Hyperlink dialog to the path and file name of the linked workbook. To link to a particular location in the other workbook, click on the Bookmark button. Now browse to the desired location as you would within the same workbook. Excel appends the sheet name and cell reference to the workbook path and name in the Address field:
Hyperlink to an Embedded Chart
You can't hyperlink to a chart directly, but you can link to a cell on the worksheet in which the chart is embedded. Use the cell under the top left corner of the chart, or the entire range under the chart.
Hyperlink to a Chart Sheet
You can't hyperlink to a chart directly, and a chart sheet has no underlying cells you can link to. You can fake it with a Worksheet_SelectionChange event procedure, however. In this example, the hyperlink is in cell B2. Enter the name of the chart sheet in cell B2, and format it with blue underlined text, so it looks like a real hyperlink. Right click on the sheet tab, select View Code, and paste this macro into the code module that appears:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then On Error Resume Next Charts(Target.Value).Activate If Err.Number <> 0 Then MsgBox "No such chart exists.", vbCritical, _ "Chart Not Found" End If On Error GoTo 0 End If End Sub
When the user selects cell B2, the procedure is activated. The code jumps to the sheet with the name in the cell. If Excel cannot go to that sheet, the code assumes it's because the sheet doesn't exist, and alerts the user.
Hyperlink to a Word Document
You can select any file to hyperlink to in the Insert Hyperlink dialog. To link to a particular bookmark in a Word document, don't click on the Bookmark button. Excel will reply with this warning: Microsoft Excel could not open this file or could not parse a file of this type. You can specify the bookmark by adding it yourself, appending an octothorpe (pound sign) plus the bookmark name to the path and file name.
To link to a particular page in a Word document, simply append a pound sign and the page number after the document path and file name:
Hyperlink to a PowerPoint Presentation
When you link to a PowerPoint presentation, it opens in SlideShow mode. By default it opens to the first slide of the presentation. If you try to use the Bookmark button to link to a particular slide within the presentation, Excel returns the same error as before: Microsoft Excel could not open this file or could not parse a file of this type. To link to a certain slide within a presentation, append a pound sign and the slide number after the presentation path and file name in the address field of the Insert Hyperlink dialog:
You can also write a macro to jump to a particular slide in a presentation. Since the hyperlink above treats me to the slide show view, I would prefer not to use it. The following procedure gets the presentation full name and slide number from A1:A2 of the active sheet, and brings up PowerPoint with the desired slide active.
Sub GotoSlide() '' Late Binding '' Presentation name and path in cell A1 '' Slide number in cell A2 Dim pApp As Object ' PowerPoint.Application Dim pPreso As Object ' PowerPoint.Presentation Dim pSlide As Object ' PowerPoint.Slide Dim sPreso As String ' Presentation Fullname Dim iSlide As Integer ' Slide Index sPreso = ActiveSheet.Cells(1, 1).Value iSlide = ActiveSheet.Cells(2, 1).Value '' Get active PowerPoint instance On Error Resume Next Set pApp = GetObject(, "PowerPoint.Application") If Err.Number <> 0 Then '' PowerPoint isn't running, so open it Set pApp = CreateObject("PowerPoint.Application") pApp.Visible = True End If On Error GoTo 0 On Error Resume Next '' get our presentation Set pPreso = pApp.presentations(sPreso) If Err.Number <> 0 Then '' our presentation isn't open, so open it Set pPreso = pApp.presentations.Open(Filename:=sPreso) End If On Error GoTo 0 '' need to be in PowerPoint normal view pApp.ActiveWindow.ViewType = 9 ' ppViewNormal If pPreso.slides.Count >= iSlide Then '' desired slide exists, so open to it pApp.ActiveWindow.View.GotoSlide Index:=2 End If '' activate PowerPoint to display the slide AppActivate pApp.Caption End Sub
Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.