Detecting double click

[kirkm]

I'm using the following to launch some VBA when a cell is double
clicked.

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

Dim rng As Range
Dim MyLastRow As String

Set rng
=Worksheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell)
MyLastRow = rng.Row

rows = "J1:J" & MyLastRow

If Not Intersect(Target, Range(rows)) Is Nothing Then
Cancel = True

********* Code here

End If


End Sub
++++++++++

Howver, I'd like to change that so any cell in the range S2 to CD5972
achieves the same result.

Is that possible?
Thanks - Kirk

 

[Tom Ogilvy]

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim sAddr as String
sAddr = "S2:CD5972"
If Not Intersect(Target, Range(s)) Is Nothing Then
Cancel = True

********* Code here

End If
End Sub

 

[Doug Glancy]

Kirk,

Excuse me for horning in, but I believe Tom meant the line to be:

If Not Intersect(Target, Range(sAddr)) Is Nothing Then

hth,

Doug

 

[kirkm]

Thanks VERY much to both of you.

That's working wonderfully

Cheers - Kirk

 

[Guest]

Yes I did. Thanks for the correction. That is the second time I have done
that <blush> (this week).

 

[Doug Glancy]

Well, I usually make tow typos in each anwser <g>

Doug