Double Click and Add ins

james.rees02

New Member
Joined
Mar 26, 2009
Messages
5
Hi,<o:p></o:p>
<o:p> </o:p>
I am creating an add-in in Excel that will be used by various people within a department. <o:p></o:p>
<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
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:p></o:p>
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:p></o:p>
<o:p> </o:p>
Any suggestions would be greatly appreciated?<o:p></o:p>
Thank!<o:p></o:p>
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi James.

Maybe with this in a standard module,
Code:
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
Code:
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
 
Upvote 0
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)
Code:
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
 
Upvote 0
Maybe?
Code:
'  '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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top