Fixed Position of Cursor Cell When Typing Enter/CTRL+Down Arrow

[diderooy]

I've Googled for an hour and have not found the answer. I suspect there is a way to do what I want, but I'm not using the right vocabulary to find it. Apologies in advance.

• I have a large spreadsheet (120,000 rows) and am modifying one column of it.
• Each row is for a given person for a given year; some people show up only once, some people twenty times.
• Individuals are already sorted by name and then by year so that I can view a few individuals' tenures simultaneously.
• In the column I am modifying, most cells are empty; some cells (20% maybe) have Xs.
• I am manually replacing the Xs with typed notes, and don't think there's any formula I can apply to plug in the needed data for every X in the column.
• Some individuals have Xs for several years of their tenure, and I need to be able to see all of their Xs at once before I start making notes for that individual.
• I need to be able to see the rows without Xs in the column I'm working on, so I can't just hide blanks to save a bunch of space.

Obviously, this involves a lot of scrolling...I can see 3-7 individuals at a time, most of the time, and then have to scroll (being careful not to scroll too far).

Is there a way to fix the position of the cursor towards the top of the pane, so that when I hit Enter (or CTRL+Down Arrow to the next X) , instead of the cursor "moving" on my screen, the spreadsheet "moves up"? I'd like to keep my hands on the keyboard because it took me a few weeks to get through the first quarter of the spreadsheet.

Again, I'm sorry. This is probably a really easy problem to solve. Thank you for your assistance.

 

[StephenCrump]

You could do this with a bit of VBA, like this: ScrollToggle.xlsm | Powered by Box

Public bToggle As Boolean
Sub Scroll()

    If bToggle Then
        ActiveWindow.ScrollRow = ActiveWindow.ScrollRow + 1
        Selection.Offset(1).Select
    End If
    
End Sub
Sub Toggle()

    If bToggle Then
        Application.OnKey "~"
    Else
        Application.OnKey "~", "Scroll"
    End If
    
    bToggle = Not (bToggle)
    
End Sub

 

[Peter_SSs]

What about a Worksheet_SelectionChange code like this? To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

My code assumes the column you are working on is column H (column 8) and you have panes frozen below row 3.
Try tweaking the red numbers below for different columns/rows

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If ActiveCell.Column = 8 And ActiveCell.Row > 3 Then ActiveWindow.ScrollRow = ActiveCell.Row - 2
End Sub

 

[diderooy]

Thanks so much to both of you!

That worked beautifully, Peter. I actually think modifying the "ActiveCell.Row" to 10 is more beneficial (or maybe even to 20), now that I've fiddled with it for a minute. But that was a very easy modification.

P.S. Thanks also for the reminder about my account settings. I think when I started on here I was using 2007, but I suspect I'll be on 365 for a while now. This is fixed.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

.. and thanks for updating your details.

 

[diderooy]

I'm sorry, I'm having trouble with this item again.

While entering some data yesterday, I had to go up to the very top of the table (above where the cursor was set to be "fixed") and got the error with the "debug" option several times while I modified some data in those uppermost rows--I don't remember what I clicked when that dialog came up, but I believe the VBA was working until I closed the document.

When I open the worksheet now, I cannot seem to get the code to work. I have tried removing it from the VBAProject screen, saving the workbook and then re-entering it, and I have tried saving the workbook as macro-free and then making it macro-enabled again. I must have disabled it somehow but I don't know how to fix it.

The VBA was working to keep me on the 20th row (or so) of column BI), and I don't believe I've changed the code displayed (besides modifying it as I did before):

 

[Peter_SSs]

Basically, the 'ScrollRow' is the row of the worksheet that will appear at the top of the screen. With your modified code, the problem is that if you selected, say, BI15 then your code is trying to make row -5 (15-20) to be the top row. Since there is no such thing as row -5 the error occurs.

Try this modification

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If ActiveCell.Column = 61 And ActiveCell.Row > 3 Then ActiveWindow.ScrollRow = ActiveCell.Row - IIf(ActiveCell.Row > 20, 20, ActiveCell.Row - 1)
End Sub

When I open the worksheet now, I cannot seem to get the code to work.

If you have not done so already, close right out of Excel (not just this workbook) and then open up again & see how that goes.

 

[diderooy]

After much frustration of trying and failing to get it to work with my workbook from Tuesday, I've dug and found a copy from Monday that I was able to start fresh with and get it working again. The problem with those top few rows seems solved as well.

Thank you for doubling back on that for me--I believe I will still come out ahead after screwing up a few hours' work yesterday haha.

Happy Thursday!

 

[Peter_SSs]

You're welcome. Glad you now have it sorted. Thanks for the follow-up.

 

[diderooy]

Just wanted to give this another bump as I think I found my problem:

When I worked on my document a bit, saved it, closed it, and reopened it, the macro was not functioning again. It turns out I had macros disabled in the Trust Center (I don't think I did that myself, either it's the default for Excel or my employer's IT set it that way). As soon as I closed the workbook--regardless of whether I saved it and saved it as an .xlsm--the macro would not work when i reopened it...the code would still be displayed but it wouldn't function. In short, I think the first code Peter gave me worked fine (except for the afore-mentioned negative row modification I made to it and he later built a workaround for).

For anyone having the same problem, check File>Options>Trust Center>Trust Center Settings>Macro Settings>"Enable VBA macros" (not recommended by MS):

I'm sure Peter would have tackled that problem next without difficulty, but I managed to score one for myself this time.