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:

C:\My Documents\Book.xls#Sheet1!A1

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.

C:\My Documents\MyDocument.doc#MyBookmark

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:

C:\My Documents\MyDocument.doc#4

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:

C:\My Documents\MyPresentation.ppt#4

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.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile