Not In Range (xp)

Topic

New Reply

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

Reply | Quote

Replies

Hi Rob,

Try something like

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

or

If Intersect(ActiveCell, MyMaze) Is Nothing Then

Reply | Quote

Hi Hans

That’s exactly what I need. Thanks.

Reply | Quote

Shouldn’t that second choice be:


If Intersect(ActiveCell, Range("MyMaze")) Is Nothing Then


Reply | Quote

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

Reply | Quote

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

Reply | Quote

In the SelectionChange event, test for whether the desired range includes the selection; something like (warning, air code)

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

Note:

• 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.
• 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]

Reply | Quote