Automatically move cursor when cell value changes
 

Using Excel 2003

I have 5 groups of 2 columns with 50 rows for each group of columns. I am
using an electronic device to input values into the cells. I have excel set
to move the cursor right after entering data.

What I want to do is when I am in the second column in a group and I enter
the data, I want the cursor to auto move back to the 2nd row in the 1st
column in that group.

For example

A b E F I J
1
2
3

When the data is entered into b1, I want the cursor to go to A2. when I
enter the data into b2, I want the cursor to move to A3 and so on down to row
50.

I would like to do this for each group of columns, i.e AB EF IJ, etc.

I have somewhat figured out how do do this, but it would take a lot of lines
of code.

Is there anyway I can do this fairly easily?

Thanks for your help

Automatically move cursor when cell value changes
 

Hi scotty:

When you type in a number and touch ENTER, the macro begins. Target is
always the address of the cell you just filled.

Target.Column is the column (as a number, not letter) of that cell.
The MOD business just makes sure we will do stuff only on the correct columns.

If you Select a cell, thats where the cursor goes to. So
Range("Z100").Select moves the cursor to cell Z100.
Cells(100,26).Select does the same thing.


But suppose you don't want to go to a specific cell, you want to go either
right or left or up or down from where we started:

Target.Offset(0,0).Select says go back to the cell you just filled.
Target.Offset(0,1).Select says go one cell to the right
Target.Offset(1,0).Select says go one cell down
etc.
--
Gary''s Student
gsnu200702


"scotty" wrote:

It didn't work the way I wanted because I wasn't explicit enough, however,
with a value change it did mostly what I wanted. Thanks!!

To be more explicit, Here is a dumbed down version of my worksheet and what
I would like to do.

A B C D E FG H I JK L M NO
1
2

Enter data into e1 and it returns to d2, and so on. Originally columns J&k
are showing, but I will hide them for purposes of entering data, when I am
finished with them columns, I will unhide them. So next when I enter the
data into M1, the cursor will return to H2 and then from M2 to H3 and so on.

I have other versions of this work sheet, but they are basically the same,
there will only be some with more groups of 2 columns.

Could you explain a little how target.column,target.offset works.

Thanks a lot for your help, that little bit of code may end up saving me a
lot of time entering data and not having to press keys.





"Gary''''s Student" wrote:

Since you only want to jump back and down after entries in columns
B,F,J,...etc:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column - 2) Mod 4 = 0 Then
Target.Offset(1, -1).Select
End If
End Sub

This is worksheet code.

--
Gary's Student
gsnu200702


"scotty" wrote:

Using Excel 2003

I have 5 groups of 2 columns with 50 rows for each group of columns. I am
using an electronic device to input values into the cells. I have excel set
to move the cursor right after entering data.

What I want to do is when I am in the second column in a group and I enter
the data, I want the cursor to auto move back to the 2nd row in the 1st
column in that group.

For example

A b E F I J
1
2
3

When the data is entered into b1, I want the cursor to go to A2. when I
enter the data into b2, I want the cursor to move to A3 and so on down to row
50.

I would like to do this for each group of columns, i.e AB EF IJ, etc.

I have somewhat figured out how do do this, but it would take a lot of lines
of code.

Is there anyway I can do this fairly easily?

Thanks for your help

Automatically move cursor when cell value changes
 

I am back at work now and trying this worksheet again.
That code is working, but has not been targeting one specific column as I
want it to so I did a little research and I think I fixed it?
I would like to add another set of code to do the same on another
specific column, yet move the cursor (1,-5), however, when I try to add that
to the worksheet code in another set of code, I get runtime/syntax? errors.
How can I set of the code to move the cursor a set # of cells when value
changes in Column E, and move the cursor a different # of cells when value
changes in column M

Thanks again for your help!!

"Gary''s Student" wrote:

Hi scotty:

When you type in a number and touch ENTER, the macro begins. Target is
always the address of the cell you just filled.

Target.Column is the column (as a number, not letter) of that cell.
The MOD business just makes sure we will do stuff only on the correct columns.

If you Select a cell, thats where the cursor goes to. So
Range("Z100").Select moves the cursor to cell Z100.
Cells(100,26).Select does the same thing.


But suppose you don't want to go to a specific cell, you want to go either
right or left or up or down from where we started:

Target.Offset(0,0).Select says go back to the cell you just filled.
Target.Offset(0,1).Select says go one cell to the right
Target.Offset(1,0).Select says go one cell down
etc.
--
Gary''s Student
gsnu200702


"scotty" wrote:

It didn't work the way I wanted because I wasn't explicit enough, however,
with a value change it did mostly what I wanted. Thanks!!

To be more explicit, Here is a dumbed down version of my worksheet and what
I would like to do.

A B C D E FG H I JK L M NO
1
2

Enter data into e1 and it returns to d2, and so on. Originally columns J&k
are showing, but I will hide them for purposes of entering data, when I am
finished with them columns, I will unhide them. So next when I enter the
data into M1, the cursor will return to H2 and then from M2 to H3 and so on.

I have other versions of this work sheet, but they are basically the same,
there will only be some with more groups of 2 columns.

Could you explain a little how target.column,target.offset works.

Thanks a lot for your help, that little bit of code may end up saving me a
lot of time entering data and not having to press keys.





"Gary''''s Student" wrote:

Since you only want to jump back and down after entries in columns
B,F,J,...etc:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column - 2) Mod 4 = 0 Then
Target.Offset(1, -1).Select
End If
End Sub

This is worksheet code.

--
Gary's Student
gsnu200702


"scotty" wrote:

Using Excel 2003

I have 5 groups of 2 columns with 50 rows for each group of columns. I am
using an electronic device to input values into the cells. I have excel set
to move the cursor right after entering data.

What I want to do is when I am in the second column in a group and I enter
the data, I want the cursor to auto move back to the 2nd row in the 1st
column in that group.

For example

A b E F I J
1
2
3

When the data is entered into b1, I want the cursor to go to A2. when I
enter the data into b2, I want the cursor to move to A3 and so on down to row
50.

I would like to do this for each group of columns, i.e AB EF IJ, etc.

I have somewhat figured out how do do this, but it would take a lot of lines
of code.

Is there anyway I can do this fairly easily?

Thanks for your help

Automatically move cursor when cell value changes
 

Thanks. I think I got it figured out.

"scotty" wrote:

I am back at work now and trying this worksheet again.
That code is working, but has not been targeting one specific column as I
want it to so I did a little research and I think I fixed it?
I would like to add another set of code to do the same on another
specific column, yet move the cursor (1,-5), however, when I try to add that
to the worksheet code in another set of code, I get runtime/syntax? errors.
How can I set of the code to move the cursor a set # of cells when value
changes in Column E, and move the cursor a different # of cells when value
changes in column M

Thanks again for your help!!

"Gary''s Student" wrote:

Hi scotty:

When you type in a number and touch ENTER, the macro begins. Target is
always the address of the cell you just filled.

Target.Column is the column (as a number, not letter) of that cell.
The MOD business just makes sure we will do stuff only on the correct columns.

If you Select a cell, thats where the cursor goes to. So
Range("Z100").Select moves the cursor to cell Z100.
Cells(100,26).Select does the same thing.


But suppose you don't want to go to a specific cell, you want to go either
right or left or up or down from where we started:

Target.Offset(0,0).Select says go back to the cell you just filled.
Target.Offset(0,1).Select says go one cell to the right
Target.Offset(1,0).Select says go one cell down
etc.
--
Gary''s Student
gsnu200702


"scotty" wrote:

It didn't work the way I wanted because I wasn't explicit enough, however,
with a value change it did mostly what I wanted. Thanks!!

To be more explicit, Here is a dumbed down version of my worksheet and what
I would like to do.

A B C D E FG H I JK L M NO
1
2

Enter data into e1 and it returns to d2, and so on. Originally columns J&k
are showing, but I will hide them for purposes of entering data, when I am
finished with them columns, I will unhide them. So next when I enter the
data into M1, the cursor will return to H2 and then from M2 to H3 and so on.

I have other versions of this work sheet, but they are basically the same,
there will only be some with more groups of 2 columns.

Could you explain a little how target.column,target.offset works.

Thanks a lot for your help, that little bit of code may end up saving me a
lot of time entering data and not having to press keys.





"Gary''''s Student" wrote:

Since you only want to jump back and down after entries in columns
B,F,J,...etc:

Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Column - 2) Mod 4 = 0 Then
Target.Offset(1, -1).Select
End If
End Sub

This is worksheet code.

--
Gary's Student
gsnu200702


"scotty" wrote:

Using Excel 2003

I have 5 groups of 2 columns with 50 rows for each group of columns. I am
using an electronic device to input values into the cells. I have excel set
to move the cursor right after entering data.

What I want to do is when I am in the second column in a group and I enter
the data, I want the cursor to auto move back to the 2nd row in the 1st
column in that group.

For example

A b E F I J
1
2
3

When the data is entered into b1, I want the cursor to go to A2. when I
enter the data into b2, I want the cursor to move to A3 and so on down to row
50.

I would like to do this for each group of columns, i.e AB EF IJ, etc.

I have somewhat figured out how do do this, but it would take a lot of lines
of code.

Is there anyway I can do this fairly easily?

Thanks for your help