Hi
I came across the same problem. There is inconsistency in TM1 here. If you drill from an In-Spreadsheet Browser View, it adds another sheet to the current workbook and you can trap that event. However, as the ISB is on the way out, we all need to use DBRWs and, if you drill from a DBRW, then it creates another workbook, and it does not fire the new workbook event.
My solution came from my user requirements. I was replacing an older design that used a double click to do the drill thru, and the users were used to this and did not like the idea of having to right click and then select Drill from a menu list of 15 or so items. So I trapped the double click event on the cell, and converted this to a call to the menu item that would be invoked by right clicking and selecting Drill. The following code sample does the trick.
Code: Select all
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If bCellIsInsideRange(Target, Range("DrillThruPoss")) Then
If Not (Application.CommandBars("cell").Controls("Drill").Enabled) Then
MsgBox ("Sorry, you cannot drill down on this cell")
Else
Call StartOfLongUpdate
Application.CommandBars("cell").Controls("Drill").Execute
If ActiveSheet.Range("A1").Value = "MyDrillThru" Then
Call DrillThruFormatSheet
End If
Call FinishedLongUpdate
End If
End If
End Sub
The check on the Range of Cells "DrillThruPoss" is not strictly necessary but is more efficient than examining the cell to see if it has an enabled Drill Thru menu straight away.
The next step accesses the CommandBars (Microsoftese for Menus), for the Cell, ie the Context Sensitive Menu you get when you Right Click. If the Drill option on the menu is not enabled, it puts up a message box and exits.
If drilling is enabled it executes the drill option on the menu. The SQL that retrieves the transactional data has a dummy first column, eg
Select 'MyDrillThru' as MyDrillThru, CostCentre, Account, etc
If the Drill was successful then a new workbook will have been opened, and this will change the ActiveSheet. The code checks the ActiveSheet to see if the first cell has MyDrillThru, and if so, then it knows that the Drill Thru was successful. It then calls a routine DrillThruFormatSheet, which does all the formatting on the ActiveSheet, which it knows will contain Drill Thru data. You can generally just record the formatting you want as a macro. I also added a bit that converted a HTML link retrieved by the SQL to a HyperText link so that the user could jump right in to the source system screen.
Using this approach gives a lot more control over what happens when you drill. The resulting report format was judged to be reasonably good.
(The StartOfLongUpdate and FinishedLongUpdate just change the cursor to an hour glass.)
Hope this helps
Regards
Paul Simon