Doubleclick event programming

[Jim]

Hello,

I am trying to set up subroutine that will activate when a user
doubleclick on a cell in a named range on worksheet.

As usual, the help file is anything but. I had done somethig similar in
word recently and thought I knew what to do - obviously I was wrong.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

In a class module named EventClassModule :

(the doubleclick was added by me to indicate that I got to the
sub)


Public WithEvents app As Application


Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, ByVal Cancel As Boolean)
Cancel = True
MsgBox "doubleclick at "
End Sub


And in a seperate module:

Public x As New EventClassModule


Public Sub InitializeApp()
Set x.app = Application
End Sub


Sub auto_open()
InitializeApp

End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


It didn't matter if I put the Workbook_SheetBeforeDoubleClick code in
the class module or in the other module ( not a class module, I forget
what to call it).




What I am gettting set up to do is anytime a user doubleclicks in a
range on worksheet1 (named schedule), the sub will open a userform to
allow controlled editing of the schedule - that I can handle, but only
if the sub will activate as needed.

I could do it with a click event as well but just for the one range
(named "fullschedule") on the worksheet and not other places in the
workbook. But I have not looked into a click event yet.


Any suggestion would be appreciated.

Jim

 

[Dave Peterson]

If you're only looking for a double click in a single range on a single
worksheet in a single workbook, you don't need the application event.

You can just use the worksheet_beforedoubleclick event.

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

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("fullschedule")) Is Nothing Then
Exit Sub
End If

Cancel = True 'stop the in cell edit
MsgBox "hi from before doubleclick"

End Sub

Right click on the worksheet tab for Schedule, select View code and paste this
into the code window that just appeared.