Excel Double Click on cell for DrillThrough

[jrizk]

Hi all. Below is code to enable double clicking on a cell to bring up the drill though selections (normally this is selected on by right clicking on a cell with the DBRW and selecting Drill). Just a neat way to use double clicking, The code needs to be on each worksheet that you want to enable this for:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

myWorkbookName = ThisWorkbook.Name
Application.Run ("Tm1p.xla!DoDrillCell")

If ActiveWorkbook.Name = myWorkbookName Then
Cancel = True
End
End If

End Sub

[Wim Gielis]

Thanks. You could shorten the code to:

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   Application.Run "Tm1p.xla!DoDrillCell"
   Cancel = (ActiveWorkbook.Name = ThisWorkbook.Name)
End Sub

I would also add a check on Target, for instance its Address property. Is the cell on which was double clicked, in a certain area? Or the .Formula property: does the cell contain a DBRW( ) formula? Etc.

Wim

[jrizk]

Good points WIm. Hadn't tested it too much but realise it needs to be only on DBRWs - so the revised code would be something like:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim pickDrill As Boolean
Dim whatFormula As String

pickDrill = False
whatFormula = "=DBRW"

If InStr(1, Target.Formula, whatFormula, vbTextCompare) > 0 Then
pickDrill = Application.Run("Tm1p.xla!DoDrillCell")
End If

Cancel = (pickDrill = False)

End Sub