How to double click a cell to open a specified worksheet in Excel?
Do you want to quickly navigate to a specified worksheet in an Excel workbook? This article will provide a VBA method to open a specified worksheet by double-clicking a certain cell in Excel.
Please do as follows to open a specified worksheet by double-clicking a cell in Excel.
1. Right-click the sheet tab which contains the cell you want to open worksheet by clicking on it. And then click View Code from the context menu. See screenshot:
2. In the opening Microsoft Visual Basic for Applications window, please copy the following VBA code into the Code window.
VBA code: double click cell to open a specified worksheet in Excel
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'Updated by Extendoffice 20180822 Dim xArray, xAValue As Variant Dim xFNum As Long Dim xStr, xStrRg, xStrSheetName As String xRgArray = Array("A1;Sheet2", "A12;Sheet3", "A4;Sheet4", "A100;Sheet5") On Error Resume Next For xFNum = LBound(xRgArray) To UBound(xRgArray) xStr = "" xStr = xRgArray(xFNum) xAValue = "" xAValue = Split(xStr, ";") xStrRg = "" xStrRg = xAValue(0) xStrSheetName = "" xStrSheetName = xAValue(1) If Not Intersect(Target, Range(xStrRg)) Is Nothing Then Sheets(xStrSheetName).Activate End If Next End Sub
Note: In the VBA code, "A1;Sheet2", "A12;Sheet3", "A4;Sheet4", "A100;Sheet5" mean that doube click cell A1 will open Sheet2, double click A2 will open Sheet3..., please change them based on your needs.
3. Press the Alt + Q keys together to close the Microsoft Visual Basic for Applications window.
From now on, when double clicking cell A1 in current worksheet, the specified worksheet will be activated immediately.
- How to click or double click cell to open a specified user form in Excel?
- How to add check mark in a cell with double clicking in Excel?
- How to auto add/enter current date/time in a cell with double clicking in Excel?
- How to double click a cell and add 1 to that cell value in Excel?
- How to pop up a calendar when clicking a specific cell in Excel?
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!