Excel - Custom cursor enter sequence via VBA

I have the attached a file displaying how the cursor should advance by hitting the Enter key using VBA.  Once the file is opened it will be very self explanatory to see the desired sequence that Excel should follow upon hitting the enter key.  From the starting cell by following the numerical order within each box and red arrows among the boxes the cursor should start from the cell with text "Cursor Starting Cell" and stop at the cell with text "Cursor Ending Cell". The goal is to provide users to enter through the page without using the mouse for their data entries.
C--Users-rerden-Desktop-CursorOrder.xlsm
trusxlsolBusiness Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$B$4"
        Range("B6").Activate
    Case "$B$6"
        Range("B7").Activate
    Case "$B$7"
        Range("J7").Activate
    Case "$J$7"
        Range("L7").Activate
    Case "$L$7"
        Range("B8").Activate
    Case "$B$8"
        Range("B10").Activate
    Case "$B$10"
        Range("B12").Activate
    Case "$B$12"
        Range("J12").Activate
    Case "$J$12"
        Range("B13").Activate
    Case "$B$13"
        Range("B14").Activate
    Case "$B$14"
        Range("B15").Activate
    Case "$B$15"
        Range("J15").Activate
    Case "$J$15"
        Range("B16").Activate
    Case "$B$16"
        Range("B18").Activate
    Case "$B$18"
        Range("B19").Activate
    Case "$B$19"
        Range("B20").Activate
    Case "$B$20"
        Range("B21").Activate
    Case "$B$21"
        Range("B22").Activate
    Case "$B$22"
        Range("F22").Activate
    Case "$F$22"
        Range("B23").Activate
    Case "$B$23"
        Range("G23").Activate
    Case "$G$23"
        Range("B24").Activate
    Case "$B$24"
        Range("L18").Activate
    Case "$L$18"
        Range("L19").Activate
    Case "$L$19"
        Range("L20").Activate
    Case "$L$20"
        Range("L21").Activate
    Case "$L$21"
        Range("L22").Activate
    Case "$L$22"
        Range("P22").Activate
    Case "$P$22"
        Range("L23").Activate
    Case "$L$23"
        Range("Q23").Activate
    Case "$Q$23"
        Range("L24").Activate
    Case "$L$24"
        Range("B28").Activate
    Case "$B$28"
        Range("B29").Activate
    Case "$B$29"
        Range("B30").Activate
    Case "$B$30"
        Range("B31").Activate
    Case "$B$31"
        Range("B32").Activate
    Case "$B$32"
        Range("F32").Activate
    Case "$F$32"
        Range("B33").Activate
    Case "$B$33"
        Range("G33").Activate
    Case "$G$33"
        Range("B34").Activate
    Case "$B$34"
        Range("L28").Activate
    Case "$L$28"
        Range("L29").Activate
    Case "$L$29"
        Range("L30").Activate
    Case "$L$30"
        Range("L31").Activate
    Case "$L$31"
        Range("L32").Activate
    Case "$L$32"
        Range("P32").Activate
    Case "$P$32"
        Range("L33").Activate
    Case "$L$33"
        Range("Q33").Activate
    Case "$Q$33"
        Range("L34").Activate
End Select
End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
Oops. I just scrolled the screen and saw that more was needed! I'll work to finish it if need be, but let me know.
0
Martin LissOlder than dirtCommented:
Here's the complete sub.
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$B$4"
        Range("B6").Activate
    Case "$B$6"
        Range("B7").Activate
    Case "$B$7"
        Range("J7").Activate
    Case "$J$7"
        Range("L7").Activate
    Case "$L$7"
        Range("B8").Activate
    Case "$B$8"
        Range("B10").Activate
    Case "$B$10"
        Range("B12").Activate
    Case "$B$12"
        Range("J12").Activate
    Case "$J$12"
        Range("B13").Activate
    Case "$B$13"
        Range("B14").Activate
    Case "$B$14"
        Range("B15").Activate
    Case "$B$15"
        Range("J15").Activate
    Case "$J$15"
        Range("B16").Activate
    Case "$B$16"
        Range("B18").Activate
    Case "$B$18"
        Range("B19").Activate
    Case "$B$19"
        Range("B20").Activate
    Case "$B$20"
        Range("B21").Activate
    Case "$B$21"
        Range("B22").Activate
    Case "$B$22"
        Range("F22").Activate
    Case "$F$22"
        Range("B23").Activate
    Case "$B$23"
        Range("G23").Activate
    Case "$G$23"
        Range("B24").Activate
    Case "$B$24"
        Range("L18").Activate
    Case "$L$18"
        Range("L19").Activate
    Case "$L$19"
        Range("L20").Activate
    Case "$L$20"
        Range("L21").Activate
    Case "$L$21"
        Range("L22").Activate
    Case "$L$22"
        Range("P22").Activate
    Case "$P$22"
        Range("L23").Activate
    Case "$L$23"
        Range("Q23").Activate
    Case "$Q$23"
        Range("L24").Activate
    Case "$L$24"
        Range("B28").Activate
    Case "$B$28"
        Range("B29").Activate
    Case "$B$29"
        Range("B30").Activate
    Case "$B$30"
        Range("B31").Activate
    Case "$B$31"
        Range("B32").Activate
    Case "$B$32"
        Range("F32").Activate
    Case "$F$32"
        Range("B33").Activate
    Case "$B$33"
        Range("G33").Activate
    Case "$G$33"
        Range("B34").Activate
    Case "$B$34"
        Range("L28").Activate
    Case "$L$28"
        Range("L29").Activate
    Case "$L$29"
        Range("L30").Activate
    Case "$L$30"
        Range("L31").Activate
    Case "$L$31"
        Range("L32").Activate
    Case "$L$32"
        Range("P32").Activate
    Case "$P$32"
        Range("L33").Activate
    Case "$L$33"
        Range("Q33").Activate
    Case "$Q$33"
        Range("L34").Activate
    Case "$L$34"
        Range("B38").Activate
    Case "$B$38"
        Range("B39").Activate
    Case "$B$39"
        Range("B40").Activate
    Case "$B$40"
        Range("B41").Activate
    Case "$B$41"
        Range("B42").Activate
    Case "$B$42"
        Range("F42").Activate
    Case "$F$42"
        Range("B43").Activate
    Case "$B$43"
        Range("G$43").Activate
    Case "$G$43"
        Range("B44").Activate
    Case "$B$44"
        Range("L38").Activate
    Case "$L$38"
        Range("L39").Activate
    Case "$L$39"
        Range("L40").Activate
    Case "$L$40"
        Range("L41").Activate
    Case "$L$41"
        Range("L42").Activate
    Case "$L$42"
        Range("P42").Activate
    Case "$P$42"
        Range("L$43").Activate
    Case "$L$43"
        Range("Q43").Activate
    Case "$Q$43"
        Range("L44").Activate
    Case "$L$44"
        Range("B48").Activate
    Case "$B$48"
        Range("B49").Activate
    Case "$B$49"
        Range("B50").Activate
    Case "$B$50"
        Range("B51").Activate
    Case "$B$51"
        Range("B52").Activate
    Case "$B$52"
        Range("F52").Activate
    Case "$F$52"
        Range("B53").Activate
    Case "$B$53"
        Range("G$53").Activate
    Case "$G$53"
        Range("B54").Activate
    Case "$B$54"
        Range("L48").Activate
    Case "$L$48"
        Range("L49").Activate
    Case "$L$49"
        Range("L50").Activate
    Case "$L$50"
        Range("L51").Activate
    Case "$L$51"
        Range("L52").Activate
    Case "$L$52"
        Range("P52").Activate
    Case "$P$52"
        Range("L$53").Activate
    Case "$L$53"
        Range("Q53").Activate
    Case "$Q$53"
        Range("L54").Activate
    Case "$L$54"
        Range("B58").Activate
    Case "$B$58"
        Range("B59").Activate
    Case "$B$59"
        Range("B60").Activate
    Case "$B$60"
        Range("B61").Activate
    Case "$B$61"
        Range("B62").Activate
    Case "$B$62"
        Range("F62").Activate
    Case "$F$62"
        Range("B63").Activate
    Case "$B$63"
        Range("G63").Activate
    Case "$G$63"
        Range("B64").Activate
    Case "$B$64"
        Range("L58").Activate
    Case "$L$58"
        Range("L59").Activate
    Case "$L$59"
        Range("L60").Activate
    Case "$L$60"
        Range("L61").Activate
    Case "$L$61"
        Range("L62").Activate
    Case "$L$62"
        Range("P62").Activate
    Case "$P$62"
        Range("L$63").Activate
    Case "$L$63"
        Range("Q63").Activate
    Case "$Q$63"
        Range("L64").Activate
    Case "$L$64"
        Range("B68").Activate
    Case "$B$68"
        Range("B69").Activate
    Case "$B$69"
        Range("B70").Activate
    Case "$B$70"
        Range("B71").Activate
    Case "$B$71"
        Range("B72").Activate
    Case "$B$72"
        Range("F72").Activate
    Case "$F$72"
        Range("B73").Activate
    Case "$B$73"
        Range("G73").Activate
    Case "$G$73"
        Range("B74").Activate
    Case "$B$74"
        Range("L68").Activate
    Case "$L$68"
        Range("L69").Activate
    Case "$L$69"
        Range("L70").Activate
    Case "$L$70"
        Range("L71").Activate
    Case "$L$71"
        Range("L72").Activate
    Case "$L$72"
        Range("P72").Activate
    Case "$P$72"
        Range("L$73").Activate
    Case "$L$73"
        Range("Q73").Activate
    Case "$Q$73"
        Range("L74").Activate
    Case "$L$74"
        Range("B77").Activate
    Case "$B$77"
        Range("B78").Activate
    Case "$B$78"
        Range("B79").Activate
    Case "$B$79"
        Range("B80").Activate
    Case "$B$80"
        Range("B81").Activate
    Case "$B$81"
        Range("F81").Activate
    Case "$F$81"
        Range("B82").Activate
    Case "$B$82"
        Range("G82").Activate
    Case "$G$82"
        Range("B83").Activate
    Case "$B$83"
        Range("L77").Activate
    Case "$L$77"
        Range("L78").Activate
    Case "$L$78"
        Range("L79").Activate
    Case "$L$79"
        Range("L80").Activate
    Case "$L$80"
        Range("L81").Activate
    Case "$L$81"
        Range("P81").Activate
    Case "$P$81"
        Range("L82").Activate
    Case "$L$82"
        Range("Q82").Activate
    Case "$Q$82"
        Range("L83").Activate
    Case "$L$83"
        Range("B4").Activate
End Select
End Sub

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

[ fanpages ]IT Services ConsultantCommented:
trusxlsol:

Are the users supposed to navigate to the range [M5:S16] within the natural order of progression around the worksheet?

Also, possibly contrary to Martin's suggestion, did you expect an edit/entry in every cell along the way, or are any of the cells optional for data capture?  That is, was your intention to simply navigate from cell-to-cell in the desired number-to-number route, without making any changes, by pressing [ENTER]?

For example, if the Application settings for MoveAfterReturnDirection is xlDown, & presently within (merged) cell [B7:H7] (that shows '3'), if you just hit [ENTER] without making any change to the cell contents, [B8:L8] is the next (merged) cell selected (showing '6'), not [J7] (showing '4') as required.

If a change is made to cell [B7:H7] ('3' is changed to 'X', for instance), then cell [J7] is correctly selected.

Can you use [SHIFT]+[ENTER] to go backwards in the numbered route, or is the direction of navigation always in one direction?

Finally, is [ENTER] the most suitable key to guide navigation in this manner?  [TAB] may make more sense, but I can see the benefit to using the [ENTER] key.
0
Martin LissOlder than dirtCommented:
If you want to you improve tabbing in the sheet you can use Format Cells->Protection and uncheck the 'Locked' textbox for all the cells where you want to allow data entry and then Protect the sheet. When you do that, tabbing, by default, will be left to right, top to bottom.
0
trusxlsolBusiness Systems AnalystAuthor Commented:
Fanpages  
The range M5:S16 is out of the scope of the [ENTER] based moving sequence as it is more optional.  But your other comment about the likelihood of the user not making any entry hence the next cell after hitting the enter key in this case is not the desired cell but the default cell per xlDown.  

Martin
Your code works just fine but I have a comment/question.  
The code is working only when an entry is made to the cell witin the given sequence.  On the other hand not every single cell may need to be populated and if there is no entry is made in that particular cell then cursor does not jump to the next required cell upon hiting the enter key but follows the default order.  Could you please modify your code so users can enter through the page whether an entry is made or not in the same desired sequence?

Thank you
0
Martin LissOlder than dirtCommented:

1.

Add a module to your workbook and add this macro.
Sub NextCellDriver()
NextCell ActiveCell
End Sub

Open in new window


2.

Cut the code from inside the Worksheet_Change and then change the sub to this
Private Sub Worksheet_Change(ByVal Target As Range)
NextCell Target
End Sub

Open in new window

3.

Add this code to the sheet and either close and reopen the workbook, or run the sub manually. Note that once this is done then any time Enter is press on the sheet the next cell in your defined sequence will be selected. To turn off Application.OnKey process then you would need to do Application.OnKey "~" somewhere.

Private Sub Worksheet_Activate()
Application.OnKey "~", "NextCellDriver" ' for the regular enter key
' If you want Enter from the numeric keypad then delete the above
' and uncomment this
' Application.OnKey "{ENTER}", "NextCellDriver"
End Sub

Open in new window


4.

Create this macro using the cut codeTo save space I've only shown the first part
Sub NextCell(cel As Range)
Select Case cel.Address
    Case "$B$4"
        Range("B6").Activate
    Case "$B$6"
        Range("B7").Activate
    Case "$B$7"
        Range("J7").Activate
    Case "$J$7"
        Range("L7").Activate
    Case "$L$7"
        Range("B8").Activate
    Case "$B$8"

Open in new window


I've attached a workbook where the above was done.
28709191.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I may not have uploaded the saved workbook so please use this one.
28709191.xlsm
0
trusxlsolBusiness Systems AnalystAuthor Commented:
It works for the most part although it still doesn't follow the sequence unless no entry is made in a cell in the numerical order good enough for now.  Will try to use this, looks like it will do for now.
0
Martin LissOlder than dirtCommented:
trusxlsol, I appreciate that you selected my post but why the "B" grade? Please see this EE article on grading which says
A "B" grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.

Please consider Requesting Attention so that a moderator can help you change the grade.
0
trusxlsolBusiness Systems AnalystAuthor Commented:
It looks like my grading appears not be aligned with EE guidelines, I explained the reason why I gave grade B instead of A on my 9/1 post, but from the EE grading rules perspective it looks like it should have been A.  My reasoning was described as "It works for the most part although it still doesn't follow the sequence unless no entry is made in a cell in the numerical order good enough for now.  Will try to use this, looks like it will do for now. "  After reading the EE rules I am convinced that the letter grade should be changed to A. My above reasoning is still valid but should not have an impact on the grade as I gather.  Like I said the current solution is working the way it is.

Please do change the letter grade from B to A for MartinLiss' solution.

Thank you
0
[ fanpages ]IT Services ConsultantCommented:
Not looking to cause ripples; just to understand the reasoning here.

A "B" grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.

Surely you are required to do some extra work to resolve your problem, because it was not fully-resolved for you.  The proposed solution did not meet your entire requirements:

It works for the most part although it still doesn't follow the sequence unless no entry is made in a cell in the numerical order good enough for now.  Will try to use this, looks like it will do for now.

"It will do" & "works for the most part" does read like you are accepting the solution because it is there, not because you are entirely happy.

However, if you are happy to proceed, & add the additional code to meet your entire requirements yourself then, as you suggested, the Grade should be increased from 'B' to 'A'.
0
trusxlsolBusiness Systems AnalystAuthor Commented:
Yes the grade should be increased from B to A.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.