Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change 'ENTER' key behaviour on different cells
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. " 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 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 a 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 " 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 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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to select cells in column enter data then press enter | New Users to Excel | |||
change autofilter behaviour? | Excel Discussion (Misc queries) | |||
Odd behaviour in dragging data across and up and down cells | Excel Discussion (Misc queries) | |||
simulate a enter key behaviour? | Excel Programming | |||
how to change (override) enter key behaviour in a cell | Excel Programming |