beforedoubleclick event does not trigger

**anneman** · 05-22-2008, 10:27 AM

I want to get a calendar object to pop up when a particular cell is double-clicked.
I have found many snippets showing this and the calendar /userform are fine but the beforedoubleclick does not run the code.
I have checked for locked cells security and protection but nothing is enabled to prevent cells from being written to. But that is assuming that the calendar would appear.
If I ran UserForm1.show it appears and a button on the calendar object enters that date into the target cell, all that is missing is to make the thing appear in the first place.
Any ideas why the event doesn't trigger the code

Thanks in advance

**dominicb** · 05-22-2008, 10:36 AM

Good afternoon anneman

...and welcome to the forum!!

In what module have you placed your click event code?

Assuming that it's in the correct place have you got any code in macros anywhere that prevents Excel from responding to events? Any add-ins installed that might be doing the same?

HTH

DominicB

**anneman** · 05-22-2008, 11:22 AM

Hi Dominic thanks for the speedy response
At first I put it in a module I added in the VBE and after I put in the THISWORKBOOK neither worked
I started with a completely new workbook as far as I am aware there are no other evenet driven modules.

This is a piece of code that I tried

Private Sub worksheet_beforedoubleclick(target As Range, cancel As Boolean)
If target.Address = "$A$1" Then
cancel = True
UserForm1.Show
End If
End Sub

**dominicb** · 05-22-2008, 11:34 AM

Hi anneman

OK. I think I know what the problem is. First, you missed of a key qualifier from your initialising routine (ByVal). I know it's only 5 letters, but it your routine won't work without it. See my alteration to your code below.

Secondly, as the event is a WorkSheet event (as opposed to a WorkBook event) it goes in the relevant sheet module. From the VBE, double click on the Sheet1 object in the project wondow and it will open a Sheet1 module window to put your code in.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$1" Then
Cancel = True
UserForm1.Show
End If
End Sub

HTH

DominicB

**anneman** · 05-22-2008, 11:49 AM

Dominic thanks for that works fine the byval thing was my mistake not paying attention having tried copying and pasting and modifying so many times.
The worksheet code was something I was musing over for a while as I wasn't sure how it differentiated over one sheet from another so i assume it's the local sheet

Thanks again