Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
Last post I showed how we can present summarized data when what our users want is supported by detail that would fill up tens of thousands of rows. When we summarized the data, in a sense, we duplicated the functionality of a simple ”pivot table.”
Pivot Tables have one feature we didn’t duplicate. It’s a feature that always astonishes new Pivot Tables users. That feature is the automatic “drill down” that happens when a value is double clicked, revealing the underlying detail. Users love this and it’s not tough to add it to the summarized sheet we created last post. The secret is in Excel’s Worksheet_BeforeDoubleClick event handler. Using this event handler, we can respond to the user double clicking on a data row and using information in that data row, pull up the detail supporting it.
Now because we are wading into deeper VBA waters, it’s appropriate to introduce some more polished coding techniques. We will be dumping Macro1() in Module1. Instead we are going to break it up into three routines.
- Worksheet_BeforeDoubleClick – This will handle the drill down request
- Prompt – Presents the user with our prompt form when they press the “easy” button.
- Get_Data – Gets and presents the data based on the parameters from Prompt or Worksheet_BeforeDoubleClick
Worksheet_BeforeDoubleClick cannot reside module1. It must be placed in the worksheet class.
Code in worksheet classes has some special properties we need to be aware of. Code in a module is shared across ALL worksheets. Routines in a worksheet are “local” to that worksheet. That means, variables declared in, and code written in a worksheet class are unique to that worksheet. Thus, we can have the same name for a routine in each worksheet with each routine meeting the specific needs of its own worksheet.
The Worksheet_BeforeDoubleClick is a fine example of this. Every worksheet has a Worksheet_BeforeDoubleClick routine. By default, they are empty – so by default, they do nothing. The Worksheet_BeforeDoubleClick in our Sheet1 will be different. We will add code so it will “drill down” into the data. But we ONLY want this behavior in Sheet1. We don’t want our “drill down” routine to respond to double clicks in our “Fields” table, and we certainly don’t want it to override our Pivot Table’s drill down functionality. Fortunately for us, a worksheet’s local event handlers ONLY respond to it and no other worksheet.
We also want to take advantage of this “local” nature of routines placed in the worksheet class. As mentioned earlier, we are going to break up the Macro1() into three separate routines. These routines will be specific to processing this particular dataset. However, in some future project, you may want to have several worksheets in a workbook with each worksheet analyzing one set of data that, with the other sets, provides a suite of analytical tools. By making the routines local, each worksheet can have its own version of Prompt and Get_Data with each version modified as needed to support the unique requirements of its own worksheet.
To add code to Worksheet_BeforeDoubleClick we must:
- Select Sheet1 from Project Explorer
- Select Worksheet from the Code Panel’s left drop down
- Select BeforeDoubleClick from the Code Panel’s right drop down
Excel will then provide a subroutine shell for us to add our specific code. And that will be the subject of our next post.