VB - BeforeDoubleClick - I want to change Boolean value of Cancel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Lori713

Thread Starter
Joined
Sep 8, 2003
Messages
50
I have a Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean) that contains instructions for certain cells/rows/columns to perform drilldowns after setting the default drilldown in the Insert,Name,Range box. This drilldown is part of PeopleSoft's nVision tool. However, I would like to stop the double-click event if the user double-clicks on a certain cell.

For example, if they double-click on cell A5, I want it to do nothing other than display a small MsgBox. If they double-click on cell B5, I want it to perform the stuff I have in my Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean).

I read in the VB help file for the doubleclick event that:

Syntax 1

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

object: An object of type Chart declared with events in a class
module. For more information, see Using Events with the
Chart Object.

Target The cell nearest to the mouse pointer when the double-
click occurs.

Cancel False when the event occurs. If the event procedure
sets this argument to True, the default double-click
action isn't performed when the procedure is finished.

The Cancel section implies that the double-click action isn't performed, which is what I want for certain cells. But, I can't figure out how to change the value so that it thinks the argument is true. I hope this makes sense. Any ideas? Thanks!

Lori
 
Joined
Oct 9, 2001
Messages
27,087
You should be able to do something like this...

Code:
If Not Application.Intersect(Target, Range("A5")) Is Nothing Then
  Cancel = True
Else
  'Perform normal actions
End If
First you check that they clicked in a cell you want to cancel the double click in, if they did, set Cancel = True (it will stop the double click from happening) otherwise perform the normal BeforeDoubleClick Event...
 

Lori713

Thread Starter
Joined
Sep 8, 2003
Messages
50
Once I wedged it into my particular situation, it works like a charm! Thanks!!!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top