Move ActiveCell

[tcarter963]

I'm trying to move the active cell to another cell when it reaches a certain point in my spreadsheet. I can get it to work when it reaches the row, but I need it to work when it reaches specific cells e.g. cells(43, 5) and cells(43,9). I think the code might do a better job of explaining.

Sub MoveTo()
 If ActiveCell = Cells(43, 5) Then
  Cells(11, ActiveCell.Column + 4).Select
 Else
  ActiveCell.Offset(1, 0).Select
 End If
End Sub

Sub bbb()
 Application.OnKey "{ENTER}", "MoveTo"
End Sub

Sub ccc()
Application.OnKey "{ENTER}"
End Sub

Private Sub Worksheet_Activate()
Call bbb
End Sub

Private Sub Worksheet_Deactivate()
Call ccc
End Sub

If I use ActiveCell.Row = 43 It works like I want it to but unfortunatly I have one row that is on row 44 that I need to include. As it is the ActiveCell moves to Cells(11, ActiveCell.Column + 4).Select no matter where it is in the column. I don't understand why it doesn't work just in cells(43, 5) and cells(43,9). I would really appreciate any help.

 

[agihcam]

what happen if you call directly the MoveTo macro from worksheet_activate event?

Private Sub Worksheet_Activate()
Call MoveTo
End Sub

 

[tcarter963]

Not much

It seems like calling directly from Private Sub Worksheet_Activate() bypasses

Sub bbb()
Application.OnKey "{ENTER}", "MoveTo"
End Sub

Which means it doesn't do anything. Maybe there is another way to call so that it includes Sub bbb that will work?

 

[tcarter963]

I tried it again, calling MoveTo in Private Sub Worksheet_Activate() and this time the ActiveCell moves to Cells(11, ActiveCell.Column + 4), like it was doing before.