Make cursor auto return to cell A1

[Legacy 32120]

Hello,

Is there a way of automating the cursor to default to cell A1 (in all sheets) when closing the workbook?

With thanks,

Max Kramer

 

[VoG]

Try this in the ThisWorkbook module

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Application.Goto reference:=ws.Range("A1"), scroll:=True
Next ws
Application.ScreenUpdating = True
End Sub

 

[Legacy 32120]

Thanks muchly VoG, that's great!

Is there a way of doing the same thing without a macro?

I'm planning to share the workbook and I'm told macros should be avoided where possible.

Cheers,

Max Kramer

 

[VoG]

I don't know how to do that without code.

I believe that that code will run correctly in a shared workbook but I believe that shared workbooks should be avoided if at all possible

 

[Legacy 32120]

I guess it will be up to my readers to choose whether or not to enable the code. It is definitely worth having this feature!

Thanks muchly again....

 

[Legacy 32120]

Strange?! The code fails after the following operations:
1) run ASAP Utilities Find/Replace, and/or
2) run the following macro.

Public Sub SpellCheckTextboxes()
Cells.CheckSpelling SpellLang:=1033
End Sub

(Reloading Excel or rebooting the system has no effect.)

With hope,

Max

P.S. BTW can resetting the cursor be done when loading the workbook?

 

[Legacy 32120]

Oooops! Sorry... I'd put the code in a new Module. Moved code to ThisWorksheet. All sheets reset to A1. But when both saving & reloading workbook it defaults to last Sheet.
How to specify default to Sheet1 please?

Thanks...

 

[VoG]

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    Application.Goto reference:=ws.Range("A1"), scroll:=True
Next ws
Sheet1.Select
Application.ScreenUpdating = True
End Sub

 

[Legacy 32120]

And thanks again, it works!