VBA for Excel: Check if cell is in range

[theDabster]

I have a range - A1 to A9 - that is not to be
altered. The macro that I'm writing moves
the active cell from wherever it is to the
very first cell in the row. I want to check
to see if the active cell has moved into this
"off limits" range. If so, then the macro
will relocate the active cell to $C$10. If
the active cell is not in this range then
other events will occur.

What VBA code would let me do this?


Thanks for your time and any help will be appreciated,

Dave
VBA Newbie

 

[kevinclark]

Add this to your ThisWorkbook module and adjust Sheet names and ranges as necessary.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim lngRow As Long
Dim intCol As Integer

lngRow = ActiveCell.Row
intCol = ActiveCell.Column
If Sh.Name = "Sheet1" Then
If intCol = 1 And (lngRow >= 1 And lngRow <= 9) Then
Sheets(Sh.Name).Cells(10, 3).Select
End If
End If
End Sub


Hope it helps

 

[theDabster]

Kevin,
Thanks for your reply. Your code was very helpful and I was able to accomplish
what I wanted to do. I've just been working with VBA for a couple of weeks now and
I don't quite have my brain wrapped around the logic and the syntax, but as I get
help from you folks and from my reading, the fog seems to be lifting a bit. The most
frustrating aspect is that I am often at a loss on how to look up stuff in the VB Editor
help file or in a book's index. I know what I want, but don't know how to &quot;ask&quot; the right
question(s). There's so many trails that can be followed.

I have yet to see the combination of ActiveCell.Row or ActiveCell.Column as you
provided and yet it's so logical once I saw your code and made things so much easier.

Much appreciation

Dave

 

[acron]

If your code is only concerned with one sheet, right click on the sheet tab concerned, select View code and enter the following :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, [A1:A9]) Is Nothing Then
        [c10].Select
    End If
    Application.EnableEvents = True
End Sub

It is always a good idea to swithch off application events when processing certain events like selection change, especially if the code changes the selection, which may be required in your case. You could find yourself in an endless loop otherwise. Event processing can be switched on again at the end.

A.C.

 

[xlbo]

Hi - not another answer but for looking up stuff in the help files, it's a good idea to look at the object model to get an idea of what properties apply to different objects. When in the VBE, press F2. This brings up the object model - have a look around it - it should help you to use &quot;help&quot;
Rgds
~Geoff~

 

[theDabster]

Geoff,
Thanks for the tip on using the help file - will definitely be a benfit.


A.C.
I appreciate your code using the intersect object and your advice on app events.


Thanks again,

Dave