If in range, on Enter key -- select cell in column A, which is scrolled to top

2Took

Board Regular
Joined
Jun 13, 2022
Messages
203
Office Version
  1. 365
Platform
  1. Windows
I have top 8 rows frozen. When I'm in row 8 in K:S and if I press Enter key, I'd like a cell in column A to be selected in whatever row happened to be scrolled to the top.
Example: row 500 may be scrolled up and be immediately under top 8 frozen rows. If I press Enter key, while in Range("k8:s8"), code should select A500. If top row is 600, then A600 should be selected.
Code I have only selects specific, hard coded cell. And with that, not only when I press Enter while in Range("k8:s8"), but anywhere on the sheet.

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(Target, Range("k8:s8")) Is Nothing Then
    Application.OnKey "~", "OnEnterK8S8"
    End If
  End Sub

Sub OnEnterK8S8()
ActiveSheet.Range("A629").Select
End Sub
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You already have the worksheet_selectionchange event set up, put it in there. Change the target to 1 row lower so when you hit enter it kicks in when you have K7:S8 selected
 
Upvote 0
You already have the worksheet_selectionchange event set up, put it in there. Change the target to 1 row lower so when you hit enter it kicks in when you have K7:S8 selected
That's not a solution, because as is your great code line runs anytime Enter is pressed, no matter what cell is selected. But I did try your advice.
It's almost as though code is completely ignoring my attempt to limit when to run.
And it's Application wide, even though the code is only in one worksheet of one workbook (probably because of line "Application.OnKey").
Seems like that only returns to normal after Excel restart, which is another issue I need a solution for -- for this modified Enter key behavior to stop, and go back to normal, once selected/active cell is not within Range("k8:s8") on a worksheet containing the code.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top