How can I define a range to detect a Double Click?

[USAMax]

I have used this subroutine to define an entire worksheet several times.

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

But I would like to be more surgical and specify specific cells. I need to detect a double click F2, F4, F6, F8 and F10, each of these cells has a border around it. If it is easier I could include everything from F2 to F10.

How can I define the range to detect a double click?

Thank you,

 

[Rick Rothstein]

I have used this subroutine to define an entire worksheet several times.

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

But I would like to be more surgical and specify specific cells. I need to detect a double click F2, F4, F6, F8 and F10, each of these cells has a border around it. If it is easier I could include everything from F2 to F10.

How can I define the range to detect a double click?

Try it like this...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Not Intersect(Target, Range("F2,F4,F6,F8,F10")) Is Nothing Then
    Cancel = True
    MsgBox "Do what you want at this location"
  End If
End Sub

 

[USAMax]

Thank you Rick,

I have never used Intersect before, now I can add it to my arsenal of tools!

Thank you!

 

[USAMax]

Hey Rick, same question different problem.
Without the Intersect line you provided I double click and it performs the function without accessing the cell. But with this line, when I double click the cursor is in the cell waiting for the user to type. I want the cell selected but not open for editing.

In case I am not being clear it is like pressing F2 on the cell after double clicking. Any suggestions?

 

[USAMax]

Good morning Rick, I hope you had a good weekend and I'm sorry to repeat my question but the program is not asking as it should.

Without the Intersect line you provided I double click and it performs the function without accessing the cell. But with this line, when I double click the cursor is in the cell waiting for the user to type. I want the cell selected but not open for editing.

In case I am not being clear it is like pressing F2 on the cell after double clicking. Any suggestions?

Thank you,

 

[Rick Rothstein]

Good morning Rick, I hope you had a good weekend and I'm sorry to repeat my question but the program is not asking as it should.

Without the Intersect line you provided I double click and it performs the function without accessing the cell. But with this line, when I double click the cursor is in the cell waiting for the user to type. I want the cell selected but not open for editing.

I am not sure what you are trying to describe to me. The "Intersect line" controls whether the code between the "If..Then" and the "End If" will be executed or not. To me, this is what the "Intersect line" is...

If Not Intersect(Target, Range("F2,F4,F6,F8,F10")) Is Nothing Then

You cannot remove it without removing the "End If" also, or you will get an error. The way the "Intersect line" works is this. Target is the cell that was double-clicked... and, because you can only double-click one cell at a time, Target is guaranteed to be a single cell reference. The Intersect function returns the address of the cell when that cell is either F2, F4, F6, F8 or F10 and the reference is Nothing otherwise. So, if the result of the intersect is "not nothing", then it must be because it returned an address which means Target is one of the cells F2, F4, F6, F8 or F10; hence, the code between the "If..Then" and the "End If" executes. The first line of that code is "Cancel=True"... that tells VB not to react physically to the double-click... that means, it is told not to enter Edit mode (what happens when the F2 control key is pressed). The other line of code between the "If..Then" and the "End If" displays your MessageBox. Hopefully that explanation will lead you to whatever it is you are attempting to do. If not, you are going to have to clarify your question for us.

 

[USAMax]

Thank you Rick you did it again,

I have always had Cancel = True in my code for the worksheet_beforedoubleclick and you had it in the code you provided me but somehow it got lost. When I put it back in it worked as expected.

My hat is off to you!