Disable Tab and Arrow Keys

[Kathleen422]

Good Morning:

Does anyone have code that treats a tab key or an arrow key response from a user as an enter response in selected columns in an Excel Spreadsheet.

Thanks for your input in advance.

 

[Loomah]

Hi
I'm not sure what you're asking for as your subject says you wan to disable keys but your message body suggests you just want to use them in a different way.

Either way it might be a bit tricky in VBA as the KeyDown, KeyPress & KeyUp events don't exist!

However if you select a cell and enter something then press one of the arrow keys or the tab key Excel will respond as if you hit Enter. The only real difference is the direction in which the cursor moves afterwards.

Having said that it is possible to change the way these keys respond. Take a look at the help files for the OnKey & SendKeys methods that I have used here.

In your worksheet module add the following

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns(2)) Is Nothing Then
    'change behaviour of TAB key in Column B
    Application.OnKey "{TAB}", "SendEnter"
Else
    'reset TAB key
    Application.OnKey "{TAB}"
End If
End Sub

In a normal module add this

Sub SendEnter()
Application.SendKeys "{ENTER}"
End Sub

Please not that this code will change the way the whole application acts so you will have to look at resetting the TAB key in the workbook Deactivate & Close events as well as testing for the current cursor position when opening or activating the book and sheet.

Hope this helps a little!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244