Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hit enter in cell & move text down in cell, not go to cell below. | Excel Worksheet Functions | |||
Cell References | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
How to Automatically Move Cell datato adjacent cell.. | Excel Discussion (Misc queries) | |||
Move automatically to a cell | Excel Discussion (Misc queries) |