Spreadsheet rows can trigger context-sensitive events by using the “BeforeDoubleClick” event. In this simple exercise we illustrate how to drill-down from an executive report to the data below.
This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.
The workbook to be used has a number of populated sheets. Please use the one found here for this exercise.
The BeforeDoubleClick Event
Whereas Excel spreadsheets are largely function-driven, VBA is event-driven; clicking on something like a button sets off a sub procedure. The BeforeDoubleClick event serves the same purpose, except that the results can be context-sensitive, based on the active cell’s value and position. For example, double_clicking Costs for Qtr3 on the summary will highlight that specific detail.
The event examines the column number of the active cell, as well as the value of the row heading (“Costs”). These provide the “compass” to a meaningful drilldown.
The Dashboard worksheet holds the BeforeDoubleClick event. In other words, this code must be placed behind the worksheet itself (navigate as highlighted below).
Copy the following code into the Dashboard spreadsheet’s code window:
'Place the code below in the code window BEHIND THE SPREADSHEET, i.e. not in a module Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Application.ScreenUpdating = False 'Hide processes 'Test to ensure the user has double_clicked within the allowable area. If Target.Column > 1 And ActiveCell.Row > 5 And ActiveCell > "" Then r = ActiveCell.Row c = ActiveCell.Column sSheet = Cells(r, 2) 'Find the relevant worksheet name in column B Sheets(sSheet).Activate Call Drilldown 'Call the code in Module1 Else MsgBox "Dbl_Click a relevant Cell" End If Application.ScreenUpdating = True End Sub
Insert a module, and copy this code into the module’s code window:
Option Explicit 'all variables used must be declared Public eRow, r, c As Integer Public sSheet As String Sub Drilldown() Sheets(sSheet).Cells(3, c).Select ‘Select row 3 and the column computed previously Selection.End(xlDown).Select 'Find the last cell in that column eRow = ActiveCell.Row 'Store the last row number in a variable 'The necessary variables are now available to highlight the relevant column. Sheets(sSheet).Range(Cells(3, c), Cells(eRow, c)).Select End Sub Sub Button1_Click() Sheets(1).Activate 'Button to return to the Dashboard. End Sub
If necessary, attach the buttons on each worksheet to the macro Button1_Click().
Test the code by double_clicking a cell. The file must be saved as an xlsm.
During the writing of this exercise, Excel’s spreadsheet, as yet unsaved, froze. The Code window was partially responsive, allowing the closure of the workbook as a whole. As it turned out, the workbook re-opened normally with the content and code complete. Had the temporary and source files been (all too frequently) damaged, the work would have had to be redone in the absence of a tool to resolve xlsx corruption. It was of little significance in this case, but could be a potential disaster for larger workbooks.
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar recovery and sql recovery software products. For more information visit www.datanumen.com