How to Utilize Conditional Events to Present Data in Different Levels of Detail

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 Code

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.

Excel Recovery

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.

Author Introduction:

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