Move ActiveCell

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
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.
Code:
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
what happen if you call directly the MoveTo macro from worksheet_activate event?
Code:
Private Sub Worksheet_Activate()
Call MoveTo
End Sub
 

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
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

New Member
Joined
Aug 3, 2006
Messages
38
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,452
Messages
5,658,857
Members
418,473
Latest member
shaztastic73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top