| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Controlling cursor w/ macro
I need to control the position of the cursor in Excel 2002, when a person
hits the ENTER key. This is a form that is being filled out. I need to first place the cursor in A3 when the worksheet becomes active. The movement after the proceeds as follows: A3, A4, A5, C4, D4, E4,....(Skip J4)...K4 As for A3-A5, only one of those cells will contain a value, in this case an "X". So if user presses the ENTER key while in A3 and advances to A4 and inputs an "X", I can skip A5. Same goes for an "X" in A3, I can skip A4 & A5. If you can give me this part, I can add the remaining code (I hope). Thanks, Les |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Controlling cursor w/ macro
Give this code a whirl. It needs to be pasted directly into the sheet so
right click the tab you want and select view code. Paste the following Private rngLastCell As Range Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Not Intersect(Target, Range("A3:A5")) Is Nothing _ And UCase(Target.Value) = "X" Then Set rngLastCell = Range("A5") ErrorHandler: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler If rngLastCell Is Nothing Then Set rngLastCell = Range("A3") Application.EnableEvents = False Select Case rngLastCell.Address Case "$A$3" Range("A4").Select Case "$A$4" Range("A5").Select Case "$A$5" Range("C4").Select Case "$C$4" Range("d4").Select Case "$D$4" Range("E4").Select Case "$E$4" Range("K4").Select Case "$K$4" Range("A3").Select End Select Set rngLastCell = ActiveCell ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "WLMPilot" wrote: I need to control the position of the cursor in Excel 2002, when a person hits the ENTER key. This is a form that is being filled out. I need to first place the cursor in A3 when the worksheet becomes active. The movement after the proceeds as follows: A3, A4, A5, C4, D4, E4,....(Skip J4)...K4 As for A3-A5, only one of those cells will contain a value, in this case an "X". So if user presses the ENTER key while in A3 and advances to A4 and inputs an "X", I can skip A5. Same goes for an "X" in A3, I can skip A4 & A5. If you can give me this part, I can add the remaining code (I hope). Thanks, Les |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Controlling cursor w/ macro
Thanks for the infor. I am confused (ignorant) about a couple of things you
have: 1) You show "Private rngLastCell As Range" before the subroutine. What does this do? 2) Not sure what you mean by right click the tab. Are you referring to the worksheet tab at the bottom? Les Thanks again? "Jim Thomlinson" wrote: Give this code a whirl. It needs to be pasted directly into the sheet so right click the tab you want and select view code. Paste the following Private rngLastCell As Range Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Not Intersect(Target, Range("A3:A5")) Is Nothing _ And UCase(Target.Value) = "X" Then Set rngLastCell = Range("A5") ErrorHandler: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler If rngLastCell Is Nothing Then Set rngLastCell = Range("A3") Application.EnableEvents = False Select Case rngLastCell.Address Case "$A$3" Range("A4").Select Case "$A$4" Range("A5").Select Case "$A$5" Range("C4").Select Case "$C$4" Range("d4").Select Case "$D$4" Range("E4").Select Case "$E$4" Range("K4").Select Case "$K$4" Range("A3").Select End Select Set rngLastCell = ActiveCell ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "WLMPilot" wrote: I need to control the position of the cursor in Excel 2002, when a person hits the ENTER key. This is a form that is being filled out. I need to first place the cursor in A3 when the worksheet becomes active. The movement after the proceeds as follows: A3, A4, A5, C4, D4, E4,....(Skip J4)...K4 As for A3-A5, only one of those cells will contain a value, in this case an "X". So if user presses the ENTER key while in A3 and advances to A4 and inputs an "X", I can skip A5. Same goes for an "X" in A3, I can skip A4 & A5. If you can give me this part, I can add the remaining code (I hope). Thanks, Les |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Controlling cursor w/ macro
That is a variable that is in scope fer every procedure in the sheet in which
it is placed. Additionally it is persistent and does not loose it's value when a procedure ends. As for the second part, yes I am refering to the work sheet tab. The code needs to be embeded right in the sheet... -- HTH... Jim Thomlinson "WLMPilot" wrote: Thanks for the infor. I am confused (ignorant) about a couple of things you have: 1) You show "Private rngLastCell As Range" before the subroutine. What does this do? 2) Not sure what you mean by right click the tab. Are you referring to the worksheet tab at the bottom? Les Thanks again? "Jim Thomlinson" wrote: Give this code a whirl. It needs to be pasted directly into the sheet so right click the tab you want and select view code. Paste the following Private rngLastCell As Range Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Not Intersect(Target, Range("A3:A5")) Is Nothing _ And UCase(Target.Value) = "X" Then Set rngLastCell = Range("A5") ErrorHandler: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler If rngLastCell Is Nothing Then Set rngLastCell = Range("A3") Application.EnableEvents = False Select Case rngLastCell.Address Case "$A$3" Range("A4").Select Case "$A$4" Range("A5").Select Case "$A$5" Range("C4").Select Case "$C$4" Range("d4").Select Case "$D$4" Range("E4").Select Case "$E$4" Range("K4").Select Case "$K$4" Range("A3").Select End Select Set rngLastCell = ActiveCell ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "WLMPilot" wrote: I need to control the position of the cursor in Excel 2002, when a person hits the ENTER key. This is a form that is being filled out. I need to first place the cursor in A3 when the worksheet becomes active. The movement after the proceeds as follows: A3, A4, A5, C4, D4, E4,....(Skip J4)...K4 As for A3-A5, only one of those cells will contain a value, in this case an "X". So if user presses the ENTER key while in A3 and advances to A4 and inputs an "X", I can skip A5. Same goes for an "X" in A3, I can skip A4 & A5. If you can give me this part, I can add the remaining code (I hope). Thanks, Les |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Controlling cursor w/ macro
Thanks for your help. I am not at the point that I am ready to insert it and
test it, but I am sure it will be what I need. Thanks, Les "Jim Thomlinson" wrote: That is a variable that is in scope fer every procedure in the sheet in which it is placed. Additionally it is persistent and does not loose it's value when a procedure ends. As for the second part, yes I am refering to the work sheet tab. The code needs to be embeded right in the sheet... -- HTH... Jim Thomlinson "WLMPilot" wrote: Thanks for the infor. I am confused (ignorant) about a couple of things you have: 1) You show "Private rngLastCell As Range" before the subroutine. What does this do? 2) Not sure what you mean by right click the tab. Are you referring to the worksheet tab at the bottom? Les Thanks again? "Jim Thomlinson" wrote: Give this code a whirl. It needs to be pasted directly into the sheet so right click the tab you want and select view code. Paste the following Private rngLastCell As Range Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Not Intersect(Target, Range("A3:A5")) Is Nothing _ And UCase(Target.Value) = "X" Then Set rngLastCell = Range("A5") ErrorHandler: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrorHandler If rngLastCell Is Nothing Then Set rngLastCell = Range("A3") Application.EnableEvents = False Select Case rngLastCell.Address Case "$A$3" Range("A4").Select Case "$A$4" Range("A5").Select Case "$A$5" Range("C4").Select Case "$C$4" Range("d4").Select Case "$D$4" Range("E4").Select Case "$E$4" Range("K4").Select Case "$K$4" Range("A3").Select End Select Set rngLastCell = ActiveCell ErrorHandler: Application.EnableEvents = True End Sub -- HTH... Jim Thomlinson "WLMPilot" wrote: I need to control the position of the cursor in Excel 2002, when a person hits the ENTER key. This is a form that is being filled out. I need to first place the cursor in A3 when the worksheet becomes active. The movement after the proceeds as follows: A3, A4, A5, C4, D4, E4,....(Skip J4)...K4 As for A3-A5, only one of those cells will contain a value, in this case an "X". So if user presses the ENTER key while in A3 and advances to A4 and inputs an "X", I can skip A5. Same goes for an "X" in A3, I can skip A4 & A5. If you can give me this part, I can add the remaining code (I hope). Thanks, Les |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Starting a Macro where the cursor is | Excel Programming | |||
| Cursor movement in a macro | Excel Discussion (Misc queries) | |||
| Controlling windows explorer with VB macro | Excel Discussion (Misc queries) | |||
| Controlling Cell/Cursor Placement | Excel Discussion (Misc queries) | |||
| controlling macro execution | Excel Programming | |||
Linear Mode
