Double Click and Add ins

[james.rees02]

Hi,<o></o>
<o> </o>
I am creating an add-in in Excel that will be used by various people within a department. <o></o>
<o> </o>
One of the functionalities I would like to add is a “drill-down” feature that upon double-clicking a number in a cell, Excel would query an Access database and return the detail that makes up the number. <o></o>
<o> </o>
My question is how to go about this? The Excel/Access interaction is not a problem, but how do I go about getting the double-clicking to work. <o></o>
I’ve tried adding the “Workbook_SheetBeforeDoubleClick” sub in the add-in, but that only works if the add-in is active. I need this to work on whatever workbook is currently active (since the add-in is hidden anyway)<o></o>
<o> </o>
Any suggestions would be greatly appreciated?<o></o>
Thank!<o></o>

 

[james.rees02]

I've also cross posted it here:
http://www.excelforum.com/excel-pro...click-procedure-in-an-add-in.html#post2069557

 

[Fazza]

Hi James.

Maybe with this in a standard module,

Option Explicit
 
Dim X As New clsApp
 
Sub auto_open()
  Application.OnTime EarliestTime:=Now + TimeSerial(0, 0, 0), Procedure:="StartMyDoubleClicker"
End Sub
 
Sub auto_close()
  Set X = Nothing
End Sub
 
Sub StartMyDoubleClicker()
  Set X.XL = Excel.Application
End Sub

And this in a class module, named clsApp

Option Explicit
Public WithEvents XL As Excel.Application
 
Private Sub XL_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Excel.Range, Cancel As Boolean)
   Cancel = True
  'your specific code
End Sub

HTH, Fazza

 

[james.rees02]

Nice. This works.
Many thanks Fazza!!

 

[Fazza]

ADVERTISEMENT

I'm pleasantly surprised it was that easy. Well done, James. Regards, Fazza

 

[james.rees02]

Thanks Fazza.
I had to modify it slightly so that users had the option to turn it on/off. This seems to work but would appreciate any suggestions.
The class module has been left untouched as per your original post.
The toggledoubleclick sub refers to a cell in the add-in that has either a 990 or null value (990 being the face id for a tick when the menu is created)

Option Explicit
Dim X As New clsApp
 
Sub StopMyDoubleClicker()
  Set X = Nothing
End Sub

Sub StartMyDoubleClicker()
  Set X.XL = Excel.Application
End Sub

Sub toggledoubleclick()
    If Workbooks("Budget Add-in.xlam").Worksheets("Add menu").Range("E11").Value = "" Then
        Workbooks("Budget Add-in.xlam").Worksheets("Add menu").Range("E11").Value = 990
        Call StartMyDoubleClicker
    Else
        Workbooks("Budget Add-in.xlam").Worksheets("Add menu").Range("E11").Value = ""
        Call StopMyDoubleClicker
    End If
    
End Sub

 

[Fazza]

Maybe?

'  'instead of
'  If Workbooks("Budget Add-in.xlam").Worksheets("Add menu").Range("E11").Value = "" Then
'    Workbooks("Budget Add-in.xlam").Worksheets("Add menu").Range("E11").Value = 990
'    Call StartMyDoubleClicker
'  Else
'    Workbooks("Budget Add-in.xlam").Worksheets("Add menu").Range("E11").Value = ""
'    Call StopMyDoubleClicker
'  End If
 
  'maybe like this
  With ThisWorkbook.Worksheets("Add menu").Range("E11")
    If .Value <> 990 Then
      .Value = 990
      Call StartMyDoubleClicker
    Else
      .ClearContents
      Call StopMyDoubleClicker
    End If
  End With