Consulting

Results 1 to 3 of 3

Thread: Intersect - very basic problem

  1. #1
    VBAX Regular
    Joined
    Feb 2010
    Posts
    14
    Location

    Intersect - very basic problem

    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 B1010) 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
    Last edited by Bob Phillips; 12-28-2013 at 08:11 AM. Reason: Added VBA tags

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Raytrace,

    You marked the thread solved. did you figure it out?

    Will you share the solution with us?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Feb 2010
    Posts
    14
    Location

    How I 'solved' it :-]

    Quote Originally Posted by SamT View Post
    Raytrace,

    You marked the thread solved. did you figure it out?

    Will you share the solution with us?
    Actually, it was a careless mistake that I was making.
    The code was working perfectly. The part where Rows.Count is being checked ( isecRange.Rows.Count > 1) was supposed to be >0, not great that one. All I did was to change the 1 to zero.

    Sometimes I think I need to drink less caffeine

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •