app_sheetbeforedoubleclick exists?

Hi, I'd like to capture doubleclicks on cells in all workbooks so I assume I should use a class module. (my app_... procedures work well for other purposes) I'd use the procedure: Private Sub App_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, ByVal Cancel As Boolean) according to the help, but when I try to compile it says: 'Procedure declaration does not match description of event or procedure having the same name.' Could you tell me what can be wrong I think I cannot use the Workbook_SheetBeforeDoubleClick or Worksheet_SheetBeforeDoubleClick procedures, as I can't assign a code dynamically to the newly created objects, (can I ) and the App_SheetBeforeDoubleClick seems to be mauch more straightforward (I would work...) Thanks! Peter

Answer this question

app_sheetbeforedoubleclick exists?

mdelatonow

Your almost there....

You need to make the event handler code an Excel addin. The addin gets loaded with Excel and it creates an intance of the class module and sets the application variable in the class module.

Here's an example...

DoubleClick.xla
Sukh
Hi, thanks. That's obvously I've done, as otherwise the other event would not work either, which I mentioned. Plust that does not help to get rid of any compilation error. The problem was that in the help the declaration was mistyped, cancel is a byref parameter not a byval one (obviously as this is an output of the procedure) reg, P
MikeBarta411
Here's some additional info from our support engineer that might be helpful:

To use Application object event, we need to follow the steps in the following link:

Using Events with the Application Object [Excel 2003 VBA Language Reference]

http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbaxl11/html/xlhowUsingAppEvents1_HV05205784.asp

I made the following test:

1. Open a new Excel workbook

2. Create a class module Class1. Insert the following code.
Public WithEvents App1 As Application

Private Sub App1_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
MsgBox "double clicked"
End Sub

Private Sub Class_Initialize()
Set App1 = Application
End Sub

3. Create a module.
Dim X As Class1

Sub InitializeApp()
Set X = New Class1
End Sub

4. Run the InitializeApp macro. When I double click any cell, "double clicked" message box is displayed.

http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlevtSheetBeforeDoubleClick1_HV05205025.asp
Application or Workbook
Occurs when any worksheet is double-clicked, before the default double-click action.

-brenda (ISV Buddy Team)
app_sheetbeforedoubleclick exists?

Answer this question