Range of Columns/Rows in DoubleClick

[Rocky0201]

Hi,

I'm trying to determine if a cell in a range of cells is double clicked. The columns are B and C and the Rows are 3 to the Last Row. My code looks like this:

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

Dim LR As Long

'-------------------------------------------------------
'Get Data
'-------------------------------------------------------

LR = Sheets("Location Master").Cells(Rows.Count, 1).End(xlUp).Row

If Target.Column = Range("2" & "3") And Target.Row = Range("3" & LR) Then

Sheets("Location Master").Activate
Call ParkAtTop("Location Master")

End If

End Sub

I'm getting a Debug Error in the Target statement when running the code.

Thanks for your help.

 

[Glory]

What's the error type you're seeing?

I'm not encountering any problem with it at all, but I don't have your subs, sheets or named ranges. I just focused on the line that used the Target, created a messagebox, and didn't have any issue.

Maybe try:

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

Dim LR As Long

'-------------------------------------------------------
'Get Data
'-------------------------------------------------------

LR = Sheets("Location Master").Cells(Rows.Count, 1).End(xlUp).Row

If Target.Column = Range("2" & "3")[COLOR=red].Column[/COLOR] And Target.Row = Range("3" & LR)[COLOR=#ff0000].Row [/COLOR]Then

Sheets("Location Master").Activate
Call ParkAtTop("Location Master")

End If

End Sub

 

[Norie]

I'm sorry but this is just wrong.

Target.Column = Range("2" & "3") And Target.Row = Range("3" & LR)

If you want to refer to the range you mention you can use this.

rngCheck = Range("B3:C" & LR)

To see if Target is in that range you can try this.

If Not Intersect(rngCheck, Target) Is Nothing Then
      ' Target is in the range rngCheck
      ' so do stuff
End If

 

[Glory]

I figured they were named ranges, Norie.

Edit: Except, that would be Range("2", "3"), wouldn't it.

And even then that wouldn't make sense, because nothing's being done with the ranges. He's just taking the value of each range, and that would have to be compared one at a time.

 

[Rocky0201]

Thanks Glory.

I still get a debug error on the target statement. Do I need to dim the ranges?

My original statement in my first post was:

If Target.Column = 2 And Target.Row = 1 Then

However, I could double click on any cell in the sheet and the code fell into my Call ParkAtTop("Location Master") routine. BTW - The ParkAtTop routines works fine,

I'm looking to only call the ParkAtTop routine if the double click is in columns b and c and rows 1 to the LR (in my spreadsheet LR is 32 but this value can change).

Thanks again for the help.

 

[Norie]

Glory

You can't have named ranges with names like '2' and '3'.

 

[Glory]

Just in case you didn't catch it, Norie had your answer.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 
LR = 32
 
Set theRange = Range("B1:C" & LR)
 
If Not Intersect(Target, theRange) Is Nothing Then
 
MsgBox "Hey"
 
End If
 
End Sub

 

[Rocky0201]

Thank you very much, Glory and Nori...