The problem I'm having is the code works great, but it won't stop jumping five rows up after hitting enter, tab, or right arrow after last entry in cell (D14) it keep jumping up to cell (E7). I need the code to stop completely after I hit enter when putting data in cell (D14) and the user can choose which method they choose to keep going across to the right of cell (D14) of their choosing. See code below, thanks!
Please Login or Register to view this content.
Last edited by ElmerFud; 07-30-2015 at 03:56 PM.
I am unable to reproduce your issue. It sounds as if you may have some cells locked and the worksheet protect, coercing a cell change.
When I add the code to my workbook and finish at D14, the focus moves to D15.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Do you mean like this?
Please Login or Register to view this content.
If you are happy with my response please click the * in the lower left of my post.
No sorry, it still jumps back to cell (E7)!Originally Posted by stnkynts
I suspect the Wabbit - can you post your spreadsheet that exhibits this phenomenon?
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
I see what you mean, it works fine in your worksheet but not mine. This worksheet does have cells locked and is protected so others users don't screw up the form or the formulas their only allowed to enter data into certain cells. Shoot, isn't there another way to stop it from jumping and just quit after D14?
The file is way to big to upload.I suspect the Wabbit - can you post your spreadsheet that exhibits this phenomenon?
I'd just make sure if D14 was the last cell changed, that it stays there:
Another way to write the same code, showcasing the Select Case structure:Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by daffodil11; 07-30-2015 at 05:03 PM.
I'd just make sure if D14 was the last cell changed, that it stays there:
Another way to write the same code, showcasing the Select Case structure:
Please Login or Register to view this content.
I tried this code too same thing happens. Darn it, there's so many other codes/modules connected to this whole workbook it's probably effecting it somehow. Ok, so I went and deleted all the remaining code modules and left only your code above. It sill does the same thing! I'll upload the file now that I deleted all the other sheets. I also deleted all "Conditional Formatting" or "Data Validation" names as well still does it?
Last edited by ElmerFud; 07-31-2015 at 12:48 PM.
Hi Elmer,
I tried it on my machine and Daffodil's code works for me. Have you ever repaired your office??
No, I haven't repaired office, how is that done? Did you down load my file and it worked with his code? I've been experimenting with my form and found out that for some unknown reason that I can't understand or explain it doesn't like cells J7:P7 merged because it works perfectly fine if you un-merge the cells. What's up with that?
I totally see it now and it's driving me bonkers.
Enter data into E4, and then cycle through. Even after my code stops the selection on D14, if you hit enter again or an arrow key it immediately select J15.
No other code or EnterKeyBehavior I see throwing this thing around. Maybe your spreadsheet is haunted?
I've been experimenting with my form and found out that for some unknown reason that I can't understand or explain it doesn't like cells J7:P7 merged because it works perfectly fine if you un-merge the cells. What's up with that?
I ran this:
And, it ran fine with no jump to J15Please Login or Register to view this content.
Your Whiteness,
The issue is not in the module code, but with the sheet code.
There's a change event forcing a selection from E4 to E11 to J2 to J4 to J6 to D14. Once you enter something in D14, the selection jumps to J15 despite no code requesting it.
To see it in action, start at E4 and enter data and cycle through.
OK - Daffodil - It happened for me finally!
Elmer - I guess repairing your Office is uncalled for since the phenomena is repeated for us
Not sure who this comment is directed to. I tried merging cells J7:K7 on your worksheet you sent me and the same thing happens. Why do you think that is? If I merge J8:K8 it works perfectly. This makes no sense!
Last edited by ElmerFud; 07-31-2015 at 04:58 PM.
Hi Elmer,
That is addressed to me (my last name is White).
Hey guys, I copied the sheet to a new book and the same thing happened
Elmer pointed out to me earlier that if you unmerge the entire worksheet, the issue goes away. I'm still baffled as to what about the merging is causing the unknown behavior.
Somehow the J7:P7 merge is making Excel thinking it needs to resume at that column.
Last edited by daffodil11; 07-31-2015 at 05:15 PM.
Very odd. If you put a Stop in at the top of the code and step through, it doesn't do it.
This seems to prevent the issue ... but not ideal:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
That's a little bit better but jumps down instead of up on occasion. Very perplexing to say the least!Very odd. If you put a Stop in at the top of the code and step through, it doesn't do it.
This seems to prevent the issue ... but not ideal:
Please Login or Register to view this content.
Regards, TMS
And, when I right arrow it jumps up
OK, another attempt.
I'm guessing, as others have, that the issue is around the fact that some cells are merged. Consequently, the Target "cell" may actually be two cells and the selection is getting confused.Please Login or Register to view this content.
It should be simpler to scale up the requirement with this approach as there are two lists of cell addresses; target cell and destination cell.
Seems to work but I'll leave it to you guys to destruction test it.
Regards, TMS
Last edited by TMS; 08-01-2015 at 06:41 PM.
Slight improvement ...
Please Login or Register to view this content.
Regards, TMS
Well I must say that's interesting, hitting tab, enter, or arrow all jumps below Cell D14. Seems like were getting closer anyways. Ideally would like it to just stop after D14 and any method used by the user works normally as it should. If you hit tab after putting in data in cell D14 it will continue on to the right, If you hit right arrow key it goes to the right of D14, If you happen to hit enter then it obviously goes directly under cell D14. Thanks to all tho, what a simple yet complex situation to find a good remedy!
Last edited by ElmerFud; 08-04-2015 at 08:23 AM.
Not in my testing. Please see the attached updated example.Well I must say that's interesting, hitting tab, enter, or arrow all jumps below Cell D14.
If this does not fix the problem for you, I suggest you restructure the sheet and lose the merged cells between columns D and E. I suspect that is the root cause of the problem.
Regards, TMS
I appreciate you trying. I downloaded the file you worked on and hitting enter, right arrow or tab jumps down one cell below cell D14. I did un-merge cells J7:P7 and it works fine. I can live with that.
OK, I give in. The only way I can get any untoward (but understandable) behaviour is if I protect the sheet and prevent selection of locked cells. But, even then, it goes where I would expect it to go. Is the sheet protected? Are you using the code in a workbook other than the sample you uploaded? Is there any other code in place, especially event handlers?
When you say:what cell is selected when you press any of those keys?and hitting enter, right arrow or tab jumps down one cell below cell D14.
Regards, TMS
If you go by my first uploaded file in the form at the Description cell J6 you type your data in and hit enter it will next jump to cell D14 which is fine the first Qty for item #1. In the normal world of excel after the last VBA code execution you should be able to hit the right arrow key and it should move to the Material cell on line item #1 and so on or if you hit tab it should stay on the same row and move to the right, hitting enter obviously will jump down to the next row or cell if you will. But it's not working that way, even in your example if you hit, enter, right arrow key or tab it jumps down one cell and one row under line item #1 Qty. Now here's the mystery if you un-merge the cells under the Description cell J6 in my original file it works perfectly. At this point it's working fine because I eliminated the merge cells J7:P7 in my original form. It becomes just a mystery now to why this happens in the first place. Don't waste anymore time on it, I appreciate it tho.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks