Use of variable address with VBA Intersect

[ElvisSteel]

Hi,

I am using Worksheet_SelectionChange to detect when users move around in my worksheet. I need to ensure they do no leave the current row of data until they have completed it fully. To this end I am using a row counter in the VBA to determine where they are and if they select a cell outside of this row, I want to return them to the required cell.

My code looks like this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 CurrentRow = Sheets("Tables").Range("CurrentRow").Value
' If cell selected not on current row, then move pointer
 If CurrentRow = 1 Then
  If Intersect(Target, Range("E6:I6")) Is Nothing Then
   Range("E6").Select
  End If
 End If
End Sub

As you can see this is a bit basic and I will have to repeat the "If CurrentRow" test for each of the 10 data rows.
Ideally I would like to be able to have the Range within the Intersect statement vary dynamically according to the CurrentRow value. I have tried using Cells instead of Range but cannot get this to work
If I can get this working I assume I can save the current row/column and return them to the same cell.

Thanks in advance

Steve

 

[RoryA]

Something like:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   currentrow = Sheets("Tables").Range("CurrentRow").Value
   ' If cell selected not on current row, then move pointer
   If Intersect(Target, Cells(currentrow + 5, "E").Resize(, 5)) Is Nothing Then
      On Error Resume Next
      Application.EnableEvents = False
      Cells(currentrow + 5, "E").Select
      Application.EnableEvents = True
   End If
End Sub

 

[ElvisSteel]

Rorya

Thanks for the prompt response, absolutely what I needed