 |
 | 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 / New Users / January 2008cursor movement within a sheet
How can I move directly control the cursor movement after pressing enter. For example, I would like my cursor movement to go something like this: CELL MOVEMENT B5 DOWN B6 DOWN B7 DOWN B8 FIRST CELL IN NEXT SECTION G5 RIGHT H5 DOWN/LEFT G6 RIGHT H6 DOWN/LEFT G7 RIGHT H7 DOWN/LEFT G8 I use protected worksheets allowing entry into unlocked cells. If I set "Move selection after Enter" direction to "right" then after B5 I end up going to G5 instead of B6. If I set "Move selection after Enter" to "down" then my entry into the "B" column goes well but then I move from G5 to G6 instead of H5 (which is where I really wanted to go next). So..... How may I more directly control cursor movement after the Enter key is pressed? :) Thanks in advance, Janet Janet One way is to use the tab key, instead of the Enter key when you make an entry. That way, the focus will move to the next unlocked cell. However, with this method Excel dictates the order of the focus movement, not you. Excel will go by rows first, then by columns. If this fits into your scheme, good. Try it out. There is another way. Below is a write-up I have on that method. HTH Otto This Way Involves Range Names: Note that you dictate the order in which the focus changes by the order in which you select the cells below. Select your SECOND cell for data entry and then hold down <Ctrl> and continue to select all the rest of the desired cells IN ORDER, ending with the FIRST cell. While the cells are still selected, click in the name box and give this range a name. You can also click Insert - Name - Define and type in the name you want. Now, when you're ready for data entry, simply click on the range name in the name box, or hit F5, select the range name you want, and click OK. The focus for the range is the first cell for data input so all you have to do now is type your data and hit enter and the focus then moves to each successive cell in your range. Just mouse click away to break out of the loop. HTH Otto > How can I move directly control the cursor movement after pressing enter. > For example, I would like my cursor movement to go something like this: [quoted text clipped - 32 lines] > Thanks in advance, > Janet Well, I've only read this and haven't tried it, but it sounds to me like there are a lot of extra keystrokes and movements that I don't want. I simply want to be able to go to the "desired" cell once I press the enter key. Are you saying there is no way to do some sort of post processing on a cell that basically does something like "go to cell g5"? > Janet > One way is to use the tab key, instead of the Enter key when you make an [quoted text clipped - 60 lines] > > Thanks in advance, > > Janet Janet You can use worksheet event code to govern the movement after you enter data and hit the ENTER key. 'moves from C2 through E5 at entry 'add cases as needed Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$C$2" Range("C5").Select Case "$C$5" Range("E2").Select Case "$E$2" Range("E5").Select End Select End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that module. Gord Dibben MS Excel MVP >Well, I've only read this and haven't tried it, but it sounds to me like >there are a lot of extra keystrokes and movements that I don't want. I [quoted text clipped - 67 lines] >> > Thanks in advance, >> > Janet Thank you, Gord! This works perfectly! :) Janet > Janet > [quoted text clipped - 91 lines] > >> > Thanks in advance, > >> > Janet Thanks for the feedback Janet. Happy to assist. Gord >Thank you, Gord! This works perfectly! :) > [quoted text clipped - 95 lines] >> >> > Thanks in advance, >> >> > Janet Gord Dibben MS Excel MVP Gord: I too am creating a protected worksheet allowing entry into unlocked cells. I did as you suggested, going TAB>VIEW CODE and pasting in my edited version of your code. (I added more cases, put in the cells' addresses, and observed the syntax that was present.) Nothing happened. I did get an Excel warning about security level, so I set the level to LOW, which allows any macro to run. Still no result. In the Visual Basic work window, there is a pulldown menu at the right side of the title bar, with 9 options. Do I need to select one of these? Do I need to include the worksheet or spreadsheet name in the code? I did a lot or programming in my previous job, but never used VB. any suggestion you (or anyone reading this) can provide would be much appreciated. ---Patrick Riley > Janet > [quoted text clipped - 91 lines] > >> > Thanks in advance, > >> > Janet Try saving your file, closing Excel, then starting up again to see if this has the desired effect. Hope this helps. Pete On Mar 28, 6:20 pm, Patrick Riley <Patrick R...@discussions.microsoft.com> wrote: > Gord: > I too am creating a protected worksheet allowing entry into unlocked cells. [quoted text clipped - 107 lines] > > - Show quoted text - I CAN REPORT SUCCESS, mostly, via 2 actions. I had already tried shutting down Excel and reopening. First off, I simply hadn't tested far enough. I have 18 protected cells or ranges. I start at cell H4. I was getting hung up trying to get to the 2nd (H6) before the 3rd (R4); it would go to the 3rd then the 2nd, and then keep yo-yo-ing between the two, never the reaching stop #4. (All of the first 3 stops are ranges.) What I hadn't checked was beyond the first 3 stops. Turns out the final 15 stops were working just fine. Secondly: So how did I get past the sticking point? I decided to swap the order between the 2nd and 3rd locked cells, and it all worked fine---thru all 18 cells. Not what I really wanted, but workable. Maybe Excel got stymied over finding its way thru a sequence where the 1st and the original 2nd stop (H6 and R4, respectively, and each a range) each resided in a SINGLE (although different) row, and the original 3rd stop, R4, spanned THREE rows plus columns. Or maybe it just wanted to go Left to Right regardless of my code. (You probably don't care about the details, but the original first 2 cells had pulldowns (using Data>Validation>List) and were situated in the upper left corner of the form, while the original 3rd, for text entry, resided in the upper right corner.) Anyway, I thank all the contributors to this thread for guiding me thru this. Patrick Riley > Try saving your file, closing Excel, then starting up again to see if > this has the desired effect. [quoted text clipped - 116 lines] > > > > - Show quoted text - I am having a similar problem and would appreciate any assistance that is available. I have copied the code and edited it accordingly. It works great for 56 cell movements but then it loops over the last three cells. It won't move on to the 57th cell. If I manually put the cursor in the 57th cell, it moves through all the rest correctly. I have tried saving, closing, and reopening but that doesn't change anything. Any suggestions? Also, is there a way to make the TAB button move the cursor? With the select case statements, you must enter something into the cell before the cursor will move. Can you make the cursor move with the cell being empty? > I CAN REPORT SUCCESS, mostly, via 2 actions. > [quoted text clipped - 143 lines] > > > > > > - Show quoted text - |
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.