Double-click macro as alternative to hyperlinks in Excel

This page describes how you can use double-click instead of hyperlinks to navigate between cells, sheets and workbooks.

Hyperlinks are handy. They let you jump to another cell or sheet just by clicking a link in a cell.

But hyperlinks have drawbacks. If you need many, it takes a long time to make them, and if you insert a row or column in the destination sheet, things get messed up, unless the link is to a named range.

This page describes how a double-click can send you to another cell with identical content or activate/open another sheet or workbook. In short: An alternative to hyperlinks.

The trick is to use the worksheet's or workbook's BeforeDoubleClick-event that will execute automatically, when you double-click. More about this below.

If you highlight the macros with the mouse, you can copy (CTRL+C) and paste (CTRL+V) the code into Excel's VBA editor.

You can also download a zipped file with examples. It contains 3 workbooks, and 2 of them (Test1 and Test2) do not have any macros. They are just there to show how you can open/activate another workbook by double-clicking a cell.

Jump to cell with identical content

Imagine that you have a workbook with 20 sheets. 19 sheets contain information about different stuff, and in column E you list suppliers/vendors. The last sheet (number 20) contains an alphabetical list of all suppliers and contact data like phone numbers, e-mails etc.

When you click a supplier name in one of the first 19 sheets, the cursor must automatically jump to the right cell in the sheet with the complete list and contact data.

To do this you could use hyperlinks, but if you insert a new supplier-row in the sheet with contact data, things will get messed up, unless the link is to a named cell.

It is easier to use the sheet's or the workbook's BeforeDoubleClick-event and add a bit of code. Right click a sheet's tab, select "View code" and insert the following code:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

'If the cell is empty or if it isn't
'column E we exit.
If Len(Target.Value) = 0 Or Target.Column <> 5 Then Exit Sub

'We now call the procedure FindName and passes
'the cell content (Target.Value).
Module1.FindName Target.Value

End Sub

In the imaginary example you could insert the code for all 19 sheets, but instead you can use the workbook's code sheet and insert the following that will execute no matter what sheet is double-clicked.

You find the workbook's code sheet by opening the VBA editor (ALT+F11). If the Project Explorer isn't open, you can open it from the "View" menu. Double-click the icon "ThisWorkbook".

Private Sub Workbook_SheetBeforeDoubleClick _
(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

If Len(Target.Value) = 0 Then Exit Sub

'If the active sheet is "Contact Data", we exit.
If ActiveSheet.Name = "Contact Data" Then Exit Sub

'If the double-clicked cell isn't in column E, we exit.
If Target.Column <> 5 Then exit Sub

'Calls the procedure FindName in Module1 and passes the cell content
Module1.FindName Target.Value

End Sub

Now it is time to insert a module with the macro that finds the cell with the supplier name in the sheet "Contact Data". It looks like this:

Sub FindName(ByVal sName As String)
'Finds and activates the first cell
'with the same content as the double-clicked cell. sName
'is the passed cell content.
Dim rColumn As Range
Dim rFind As Range

'Activate the sheet Contact Data.
Worksheets("Contact Data").Activate

'Set the range rColumn = column A
Set rColumn = Columns("A:A")

'Search column A
Set rFind = rColumn.Find(sName)

'If found the cell is activated.
If Not rFind Is Nothing Then
   rFind.Activate
Else
   'If not found activate cell A1
   Range("A1").Activate
End If

Set rColumn = Nothing
Set rFind = Nothing

End Sub

Jump to another sheet

This is quite simple - the macro jumps to another sheet, if the double-clicked cell contains the sheet name. In the example it is a condition that the double-clicked cell must be in column C. Here it is code for a single sheet.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

'If the cell is empty or not in column 3 (C), we exit.
If Len(Target.Value) = 0 or Target.Column <> 3 Then Exit Sub

On Error Resume Next
Worksheets(Target.Value).Activate

End Sub

We write "On Error Resume Next", because it will trigger an error if the cell content doesn't match the name of a sheet.

Activate or open another workbook

By double-clicking a cell you can also activate or open another workbook, if the workbook name is in the cell. The example below requires the cell to be in column A, and the other workbook must be in the same folder.

The cell must contain the workbook's "first name" only, which in this case is "Test1". The file extension (e.g. .xls) should not be there.

As before we need some code in the sheet's BeforeDoubleClick event, and the we need 2 macros in Module1. First the sheet's BeforeDoubleClick event:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

'We exit if the cell is empty or not in column A.
If Len(Target.Value) = 0 Or Target.Column <> 1 Then Exit Sub

'Call the procedure "ActivateWorkbook" in Module1
Module1.ActivateWorkbook Target.Value

End Sub

Sub ActivateWorkbook(ByVal sWbName As String)

On Error GoTo ErrorHandle

'Call the function BookIsOpen to check if the workbook is open.
If BookIsOpen(sWbName) Then
   'Activate if open.
   Workbooks(sWbName).Activate
Else
   'If it isn't open, we check if it is
   'in the same folder as this workbook.
   'If it is, we open it.
   If Len(Dir(ThisWorkbook.Path & "\" & sWbName & ".xl*")) > 0 Then
      Workbooks.Open (sWbName)
   Else
      MsgBox "Workbook " & sWbName & _
      " not found in " & ThisWorkbook.Path
   End If
End If

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure ActivateWorkbook, Module1"
End Sub

Function BookIsOpen(sWbName As String) As Boolean
'If the workbook is not open, the following
'will trigger an error, so we write "On Error Resume Next".
On Error Resume Next

BookIsOpen = Len(Workbooks(sWbName).Name)

End Function

That was it - examples on how to "jump" or navigate in and between sheets and workbooks by double-clicking a cell.

There are many other possibilities than jumping. The cell could get a certain colour, or the text could be formatted some other way. It all boils down to the fact that certain events can trigger the automatic execution of code without you having to click a button or whatever.

Sheets and workbooks have other events than BeforeDoubleClick. If you right-click a sheet's tab and selects "View code", the VBA editor opens. It will say (General) in the top left ComboBox, and if you change that to "Worksheet", you can see the possible events in the top right ComboBox.