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

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


"(E-Mail Removed)" wrote:

> 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
>
>

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.


On May 25, 7:46*pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "Hammer...@gmail.com" wrote:
> > 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

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


"(E-Mail Removed)" wrote:

> 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.
>
>
> On May 25, 7:46 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "Hammer...@gmail.com" wrote:
> > > 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
>
>

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...


On May 26, 12:25*am, JLGWhiz <JLGW...@discussions.microsoft.com>
wrote:
> 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
>
>
>
> "Hammer...@gmail.com" wrote:
> > 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.
>
> > On May 25, 7:46 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > 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 toapply
> > > 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
>
> > > "Hammer...@gmail.com" wrote:
> > > > 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 makinga
> > > > 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 ona
> > > > 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

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.

"(E-Mail Removed)" wrote:

> 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...
>
>
> On May 26, 12:25 am, JLGWhiz <JLGW...@discussions.microsoft.com>
> wrote:
> > 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
> >
> >
> >
> > "Hammer...@gmail.com" wrote:
> > > 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.
> >
> > > On May 25, 7:46 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > > 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
> >
> > > > "Hammer...@gmail.com" wrote:
> > > > > 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
>
>

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

"(E-Mail Removed)" wrote:

> 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...
>
>
> On May 26, 12:25 am, JLGWhiz <JLGW...@discussions.microsoft.com>
> wrote:
> > 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
> >
> >
> >
> > "Hammer...@gmail.com" wrote:
> > > 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.
> >
> > > On May 25, 7:46 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> > > > 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
> >
> > > > "Hammer...@gmail.com" wrote:
> > > > > 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
>
>