 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / May 2008Change 'ENTER' key behaviour on different cells
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 > Hi, > [quoted text clipped - 26 lines] > > 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. > 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 [quoted text clipped - 43 lines] > > > 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 > Thanks for the reply. > [quoted text clipped - 76 lines] > > > > > 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 [quoted text clipped - 98 lines] > > > > > 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. > This is not working for me... > [quoted text clipped - 113 lines] > > > > > > > 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 > This is not working for me... > [quoted text clipped - 113 lines] > > > > > > > Thanks Much much better. Thanks. How do you capture cell I3 so it calls a Sub? On May 26, 11:40 am, JLGWhiz <JLGW...@discussions.microsoft.com> wrote: > This might work better. > [quoted text clipped - 4 lines] > End If > End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.