raytrace
12-28-2013, 01:52 AM
I am trying to use Intersect to detect change in a certain range.
I applied the code to the Worksheet's "Worksheet_SelectionChange" event.
The code seems to works perfectly save for one glitch that I cannot seem to sort out.
Whenever I try to select multiple rows and columns overlapping the given range (C3:C30) that Intersect works fine.
It even works perfectly if I select only one cell inside the range (e.g C10).
However, when I select a single row (e.g B10:D10) the Intersect does not recognize it as anything.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim watchrange2 As Range
Dim isecRange As Range
Set watchrange2 = Range("C3:C30") 'same as in CHANGE WORKSHEET event
'check if currentlyselected area has any overlapping cell in wathrange range
Set isecRange = Application.Intersect(Target, watchrange2)
If isecRange Is Nothing Then
'do nothing
Else
If isecRange.Rows.Count > 1 Or isecRange.Columns.Count > 1 Then
'selected area is intersecting the watched range, and has either rows or columns exceeding 1
'|--> stop it right here
MsgBox ("Target (range):" & Target.Address & ", isecRange=" & isecRange.Address)
Target(1, 1).Select
Else
OldVal = Target.Value 'to retain value pre-change
End If
End If
End Sub
I applied the code to the Worksheet's "Worksheet_SelectionChange" event.
The code seems to works perfectly save for one glitch that I cannot seem to sort out.
Whenever I try to select multiple rows and columns overlapping the given range (C3:C30) that Intersect works fine.
It even works perfectly if I select only one cell inside the range (e.g C10).
However, when I select a single row (e.g B10:D10) the Intersect does not recognize it as anything.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim watchrange2 As Range
Dim isecRange As Range
Set watchrange2 = Range("C3:C30") 'same as in CHANGE WORKSHEET event
'check if currentlyselected area has any overlapping cell in wathrange range
Set isecRange = Application.Intersect(Target, watchrange2)
If isecRange Is Nothing Then
'do nothing
Else
If isecRange.Rows.Count > 1 Or isecRange.Columns.Count > 1 Then
'selected area is intersecting the watched range, and has either rows or columns exceeding 1
'|--> stop it right here
MsgBox ("Target (range):" & Target.Address & ", isecRange=" & isecRange.Address)
Target(1, 1).Select
Else
OldVal = Target.Value 'to retain value pre-change
End If
End If
End Sub