VBA for TAB Control

[oblix]

I would like to be able to have no tab control (Does not move down after pressing enter but rather remain in cell) on sheet 6 and sheet 7 only.
On all other sheets it must move down after enter.
Is this possible with vba.
Thanking all in advance for answer.

 

[Jimmypop]

Hi there

Any reason for doing this? It is a bit risky seeing as you might code wrong somewhere and then would need to fix repair Excel...

 

[oblix]

Hi there
Yes
Many cells in those sheets automatically pops up a dropdown list when selected. Every time a have selected/entered in a cell i dont want to by default have the next pop up in my face.
Thank you for asking

 

[Jimmypop]

Ok... and should this be on Enter button or Tab button?

 

[bebo021999]

Right click on Sheet6/ view code, then paste below code into:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Select
Application.EnableEvents = True
End Sub

Same process for sheet7

 

[Colo]

Here is a sample for Tab and Enter keys.
Place the following procedures in a sheet module for Sheet6 and Sheet7.
If you really want to control users' behavior, need to handle other keys such as allow keys. It seems to be a hassle.

Private Sub Worksheet_Activate()
    Application.OnKey "{TAB}", ""
    Application.OnKey "~", ""    'Enter
    Application.OnKey "{ENTER}", ""    'Enter in Numeric keypad
End Sub

Private Sub Worksheet_Deactivate()
    Application.OnKey "{TAB}"
    Application.OnKey "~"    'Enter
    Application.OnKey "{ENTER}"   'Enter in Numeric keypad
End Sub

 

[oblix]

Thank you for all answers
will try all as soon as possible
Is it possible to deactivate on leaving the sheet automatically?

 

[oblix]

I realize now that it is actualy on pressing enter I want to remain in cell and not when pushing TAB
Sorry for mislead
When I press on other sheets I would like to return to normal (move one down after enter)

 

[Colo]

I believe that it would work as intended. Just try to delete some lines related to Tab from the procedures that I posted. Have you tried our codes?