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
C--Users-rerden-Desktop-CursorOrder.xlsm
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.
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![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
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.
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I may not have uploaded the saved workbook so please use this one.
28709191.xlsm
28709191.xlsm
ASKER
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.
trusxlsol, I appreciate that you selected my post but why the "B" grade? Please see this EE article on grading which says
Please consider Requesting Attention so that a moderator can help you change the grade.
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.
ASKER
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
Please do change the letter grade from B to A for MartinLiss' solution.
Thank you
Not looking to cause ripples; just to understand the reasoning here.
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 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'.
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'.
ASKER
Yes the grade should be increased from B to A.
Open in new window