Left arrow key will not behave

davidam

Active Member
Joined
May 28, 2010
Messages
452
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:
Code:
If  Target.Row = 49 Or Target.Row = 58 Then
 Application.OnKey "{Up}", "NextUp_toRow31"
 GoTo UpArrowEnd
End If

and then, in a standard module:
Code:
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:
Code:
Application.OnKey "{RIGHT}", "MoveRight"

and the following is a standard module:
Code:
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

DushiPunda

Well-known Member
Joined
Nov 14, 2015
Messages
508
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

Active Member
Joined
May 28, 2010
Messages
452
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

Active Member
Joined
May 28, 2010
Messages
452
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

Active Member
Joined
May 28, 2010
Messages
452
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
 
Last edited:

davidam

Active Member
Joined
May 28, 2010
Messages
452
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.
 
Last edited:

Forum statistics

Threads
1,144,629
Messages
5,725,387
Members
422,622
Latest member
Paranthem

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