Left arrow key will not behave

[davidam]

Hello All,
In order to make the 'Up' and 'Down' arrow keys work properly, I am using some code like the following:
First, the Worksheet Selection Change event:

If  Target.Row = 49 Or Target.Row = 58 Then
 Application.OnKey "{Up}", "NextUp_toRow31"
 GoTo UpArrowEnd
End If

and then, in a standard module:

Public Sub NextUp_toRow31
If ActiveCell.Address = "$AA$58" Then
  If Range("E1").Value = 2 Then 
        Range("AF49").Select
        GoTo End31
   Else
        Range("AF41").Select
        GoTo End31
   End If
End If
If ActiveCell.Address = "$AF$49" Then
        Range("G47").Select
        GoTo End31
End If
....

My problem is that the left and right arrow keys will then want to select the left or right (unlocked) cell on the previous row, rather than on the newly selected one. I have been able to manage the right arrow key with the following:
First, at the top of the Worksheet Selection Change module:

Application.OnKey "{RIGHT}", "MoveRight"

and the following is a standard module:

Public Sub MoveRight()
Application.SendKeys "{Tab}"
End Sub

The problem is the Left arrow key. I have tried the same with "+{Tab}" but it does not help. I have tried re-selecting the active cell after the standard sub has run, etc, etc. I cannot find anything that works. Any ideas?
Thanks,
David

 

[DushiPunda]

So, with your MoveRight code...simply sending a Tab will select the right (unlocked) cell on the previous row?

If you go to the cell where you would initiate your MoveLeft code and manually press SHIFT + TAB, does it take you where you want it to take you?

Any particular reason you're trying to do this with SendKeys instead of selecting the appropriate cells using some sort of combination of logic and the .Select method?

 

[davidam]

In fiddling around further, I find that the Shift + Tab has the same kind of issues when I invoke them manually as it does with the SendKey.
As to reasons: I have a large sheet which serves as a data input form. I have used a logic and select method (as per the top of my post) for the up and down keys because many/most unlocked cells do not line up in the same columns; this works fine but it was a lot of work. For the left and right keys, however, there are many instances where a simple right or left move should suffice. Instead I end up with this bizarre behavior.
Of note is that it only happens the first time I use the left or right key using the aforesaid method at the top of my post... then t behaves normally. I think, for some reason, I need to figure out how to 'normalize' the Right and Left keys after using the above method.

 

[davidam]

I have placed a series off debug.prints throughout the code. It never makes a mistake as to what row is active. It just randomly decides that, when I use the Left arrow key (after using the above method) it will select a different row. If I have used the up key twice before using the left key , it will use the row prior to the first use of the up key. This would appear to be some kind of advance built in logic to simplify my life. Must be some way to turn it off.

 

[davidam]

After much deep searching, I could find nothing that resolved this issue. Eventually I adapted the Worsksheet.Selection.Changes to accommodate the left and right arrow keys. I found this module to be very accommodating of Ands, Ors and Select Cases such that the code was quick and easy and it all works like a charm. Thanks DushiPunda

 

[davidam]

Further to this if anyone happens along. It occurred to me that for each of the navigation keys that I want to control, it would be more efficient to have just the OnKey command in the Worsksheet.Selection.Changes module and everything else in standard modules. Having now done so, the arrow keys are acting perfectly with no discernible lag. I thought the large amount of code in worksheet module might be causing some issues with the un-programmed keys. I am pretty sure that is not the case as I am still getting the erratic behavior on the tab key which I have not you programmed. I think that with a sheet like this (a series of non-contiguous, locked and unlocked cells), one can probably expect some unpredictable behavior on these keys.