Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not In Range (xp)

    I have a range on a worksheet (A1:F15) . In code, this is referred to as MyMaze. How can I flag if a cell outside this range is selected?
    TIA
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not In Range (xp)

    Hi Rob,

    Try something like

    If Intersect(ActiveCell, Range("A1:F15")) Is Nothing Then

    or

    If Intersect(ActiveCell, MyMaze) Is Nothing Then

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In Range (xp)

    Hi Hans

    That's exactly what I need. Thanks.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not In Range (xp)

    In the SelectionChange event, test for whether the desired range includes the selection; something like (warning, air code)
    <pre>private sub Worksheet_SelectionChange(byval Target as range)

    if intersect([mymaze],target) is nothing then
    ' code to perform if selection is outside "MyMaze"
    else
    'code if selection is inside "MyMaze"
    end if

    end sub
    </pre>

    Note:
    <UL><LI>the method to refer to the area where you want to confine the selection will depend on how it is used or already defined - this example assumes that it is a named range within excel, but if it already a range object you can just use the name.
    <LI>there is a flaw in this test if you are going to allow multi-cell selections - in this case, part of the selection could be within MyMaze, and part could be outside - you have to decide what to do with such a selection, and then test for it - possibly by testing for "if union([mymaze],target) = mymaze" - this will be true only if target is entirely contained within mymaze[/list]

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In Range (xp)

    Shouldn't that second choice be:

    <code>
    If Intersect(ActiveCell, Range("MyMaze")) Is Nothing Then
    </code>
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not In Range (xp)

    I assumed that MyMaze was a variable of type Range. If it is a named range instead, your code is correct.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In Range (xp)

    I assumed a named range. The OP is not very clear.
    Legare Coleman

Posting Permissions

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