BeforeDoubleClick doesn't seem to work

[Dan]

I have this code in my project, and double clicking on the sheet doesn't
appear to do anything:

Public Sub BeforeDoubleClick(ByVal SelRange As Range, Cancel As Boolean)
Cancel = True
msgbox "Range= " & SelRange
End Sub

What am I doing wrong?

Thanks -
Dan

 

[Jay]

Hi Dan -

Change "BeforeDoubleClick" to "Worksheet_BeforeDoubleClick" and see what
happens.

 

[NickHK]

Dan,
You cannot make up events and expect Excel to fire them. It is best to let
Excel create the routine stubs for you to ensure they are correct.
On the worksheet module, select Worksheet from the top-left combo box then
select the required event from the top-right combo box.
See the difference:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True
MsgBox "Range= " & Target
End Sub

Also, I avoid using default properties. Be explicit.
It looks like you want
Target.Address
but you are getting
Target.Value

NickHK

 

[Dan]

Thanks Nick -
My top left combo box doesn't contain anything other than "(General)", and
the right one has all of my subs listed, but nothing else.
How do I get these combo boxes to give me other options?
Thanks!

 

[Dan]

Another thing - in the syntax for the function, Target is supposed to be a
range. However, Target contains the value of the cell that was dbl-clicked.
Not the range as I expected.
I need to determine the cell name that was dbl clicked, then determine if
the cell is inside a specific range or not.

Why is Target the cell value, not it's range?

Thanks -

 

[Jay]

Hi Dan -

It sounds like you are on the right track, but in the wrong place. Based on
what you are seeing in the comboboxes (General and your procedure names), you
are in a General module. As Nick instructed earlier, you need to develop
your BeforeDoubleClick event procedure on a Worksheet module instead.

1. To get there, open the VB Editor and look in the Project Explorer (in the
left-hand pane).

2. Find your project, open the folder named "Microsoft Excel Objects", and
double-click on the worksheet where your BeforeDoubleClick procedure will
apply. This opens a worksheet module where you can build your
BeforeDoubleClick event procedure.

3. Change the left-hand combobox at the top of the main editor window to
"Worksheet". Then, the right-hand combobox will show a list of events.
Choose BeforeDoubleClick and the editor will produce a procedure a valid code
template for you to enhance as Nick has described.

4. See my response to your next post for a sample of code. Make sure you
copy it to the Worksheet module.

 

[Jay]

Hi Dan -

VBA does refer to ‘Target As Range’ because it is a range, but by default
rules of syntax for the BeforeDoubleClick procedure, it converts Target to
it’s value using ByVal. The conversion imparts a measure of protection
against unintentionally modifiying Target’s cell contents in the procedure.
You can’t modify the cells contents simply referring to ‘Target’. It must be
done explicitly somehow, ensuring that changes are intentional.

Modifying any component of the argument list results in an error. This is
the reason Nick stressed the technique of ‘letting excel create the routine
stubs.’ The VB editor sets the ground rules for the procedure by
automatically providing a syntax ‘boilerplate’; violating the boilerplate
ground rules results in errors.

That being said, once created, the procedure can be enhanced and copied as
long as you don’t edit the ‘boilerplate’ syntax. Below is a stab at your
latest specifications. Copy it to the proper worksheet module and modify the
range in the third line…
------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Cancel = True

Set containmentRng = Range("A1:A79") ' <==enter your sepcific range here.
Set isect = Application.Intersect(Cells(Target.Row, Target.Column),
containmentRng)

If isect Is Nothing Then
MsgBox "Double-clicked cell IS NOT inside containment range."
Else
MsgBox "Double-clicked cell IS inside containment range."
End If

End Sub

 

[Dan]

Nick, Jay, thank you both. I have it working now. I appreciate your patient
explanations. I am only a novice VB programmer, using it for little apps to
help with work.
I have a new challenge... I'll be posting it. Been working on it all day and
can't make it work.

Dan