vb code to remain in same cell after hitting enter

santa12345

Board Regular
Joined
Dec 2, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hi.
I am struggling with this one. Hopefully its somewhat easy.
I know you can change the file options within excel to remain on a specific cell after entering data..and hitting enter.
But thats for all workbooks, etc...
Is there vb code to make this happen on only one workbook?
I want to enter data in cell A1 and hit enter...and the cursor stays in cell A1.
When I close the workbook and open up another file, I want the standard Excel default to act as normal. Hitting enter takes you down a cell.
I thought I could put the Application.MoveAfterReturnDirection command within my particular .xls file.. but I can't get it to work.
Any input would be appreciated.
Thanks.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,629
Office Version
  1. 2016
Platform
  1. Windows
I think there are several possible way to do this. Is you macro is event triggered or key triggered?.

If event triggered then can just use Target to return to same position. Example:
VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Select
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,698
Office Version
  1. 365
Platform
  1. Windows
What if you put these codes into the ThisWorkbook module of that workbook?

VBA Code:
Private Sub Workbook_Activate()
  Application.MoveAfterReturn = False
End Sub

Private Sub Workbook_Deactivate()
  Application.MoveAfterReturn = True
  Application.MoveAfterReturnDirection = xlDown
End Sub
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,629
Office Version
  1. 2016
Platform
  1. Windows
What if you put these codes into the ThisWorkbook module of that workbook?

VBA Code:
Private Sub Workbook_Activate()
  Application.MoveAfterReturn = False
End Sub

Private Sub Workbook_Deactivate()
  Application.MoveAfterReturn = True
  Application.MoveAfterReturnDirection = xlDown
End Sub
I wonder why he said it did not work when he tried to put that command :unsure:
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,698
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I wonder why he said it did not work when he tried to put that command :unsure:
Only mentioned was Application.MoveAfterReturnDirection
So some possibilities could be ..
- Application.MoveAfterReturn was not switched on/off
- Code was not put in the correct module
- One code was used but not the other
- Macros disabled
 

santa12345

Board Regular
Joined
Dec 2, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Thank you for the replies! A few solutions were provided as well as the one below:
Have a good day....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Select
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,698
Office Version
  1. 365
Platform
  1. Windows
It may not matter to you but according to your thread title and your description in post #1 your question was to alter the behaviour in this workbook when "hitting Enter".
When entering values in a cell, as well as hitting Enter, you can instead hit Tab (generally moves right) or you can use a mouse-click (to move immediately to any cell of your choice).
The change event codes suggested will also over-ride the Tab or mouse-click entry methods too.

As I said that may well be what you want but note that the code I suggested would preserve the existing behaviour for those other entry methods, and only change it for 'Enter'.

One other thing to possibly consider is that since you are using vba for this I'm wondering if you are, or might in the future, use vba for something else in this workbook. If you do, and that vba changes the value of any cell that is not on the Active Sheet, then the SheetChange event code will throw an error.
 

Forum statistics

Threads
1,141,857
Messages
5,709,027
Members
421,605
Latest member
Danny_G

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
Top