Change 'ENTER' key behaviour on different cells

[HammerJoe]

Hi,

I need help for some code.

I want to use the 'ENTER' key to behave differently when the selection
change on some columns.

Let me explain.

In row 3 I use columns B to I to enter data.
Columns B, F and H are in cell drop down validation cells (list).
What I would like to do is when a selection is made by either making a
selection of the list or typing it manualy and then when "ENTER" is
pressed to automatically move to the next colum on row 3.

On a side note, is it possible when manually typing the selection on a
in cell drop down validation cell to ignore caps? Ie on column H the
choices are Yes,No,Outstanding and if I type it without the first
letter on CAP it doesnt work, I get an error message that the value is
not correct.

Anyway, columns C, D, E, G and I are just alphanumeric cells so when
enter is pressed then move to the right on Row 3.
Finally on column I when "ENTER" is pressed I want to call a macro
that will copy the data to the first available row starting at row 7
column B.

How can I do this?


Thanks

 

[JLGWhiz]

For the cursor movement, you would probably have to use the Worksheet_Change
event and make it conditional for the range of cells where you want to apply
it. The direction is controlled by the MoveAfterReturnDirection Property.

For the problem with enterin Caps or non Caps, you have to control that with
either the UCase or LCase function by making either entry (Upper or Lower)
equal to only one and then make the executable statement for that value
recognize only the one that you choose to use. Example.

Range("A1").Value = LCase(Range("A1").Value
If LCase(Range("A1").Value) = "a" Then
'Do something
Else
'Didn't = "a"
End If

 

[HammerJoe]

Thanks for the reply.

I have this so far:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If ActiveCell.Row = 3 Or ActiveCell.Row = 4 Then
If ActiveCell.Column >= 2 And ActiveCell.Column <= 8 Then
Application.OnKey "{ENTER}", NextCol
End If
End If
End Sub

Public Function NextCol()

'If ActiveCell.Row = 3 Then
Select Case ActiveCell.Column
Case 2, 6, 8
ActiveCell.Offset(0, 1).Select
Case 3, 4, 5
ActiveCell.Offset(0, 1).Select
Case 9
MsgBox "add to new row"
End Select

It seems to work, except for which "enter" that is pressed.
If it is the numpad 'enter' then it works but on the main keyboard
when pressing "Enter" the cursor moves up one cell, so now I have to
check which "Enter" has been pressed.
Unless there is a way to make both "Enter" ebhave the same way on this
workbook only?

Thanks for any help.

 

[JLGWhiz]

This might not satisfy your needs but this is more what I had in mind. The
problem with this is that it does not immediately change direction on the
first keystroke for Enter. It takes effect on the second keystroke.
However, it will continue to move to the right until you move the cursor
outside the target area and make a change to a cell. This sets the direction
to down if it is outside the target area, but you can easily change it to any
other direction. The options are:

xlUp, xlDown, xlToLeft and xlToRight.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Intersect(Target, Range("B3:H4"))
If Not Target Is Nothing Then
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlToRight
Else
Application.MoveAfterReturn = True
Application.MoveAfterReturnDirection = xlDown
End If
End Sub

 

[HammerJoe]

This is not working for me...

The issue is that when the Worksheet_Change is called the cursor might
not be in the correct cell anymore.
What I need is when that SUB is called is to have the Cell that has
changed and then move the cursor from there.

Another issue is that Application.OnKey "{ENTER}", NextCol still calls
NextCol even if a cursor key (Right) is used??
Why is that?

A simple thing is turning into a complete frustration for me...

 

[JLGWhiz]

This is a clunge to force the cursor to the right when a change is made
within the designated range. It move right ONLY if there is a change.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Intersect(Target, Range("B3:H4"))
If Not Target Is Nothing Then
ActiveCell.Offset(-1, 1).Activate
End If
End Sub

I think it is about as close as I can get to what you want.

 

[JLGWhiz]

This might work better.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Intersect(Target, Range("B3:H4"))
If Not Target Is Nothing Then
Target.Offset(0, 1).Activate
End If
End Sub