Is it possible to, on save and exit, to get the cursor positioned to
cell A1 on every sheet?

Thanks for any help


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=508517

ctrl home puts the cursor back to a1,if you want it to happen automatically
record a macro doing ctrl home and then make it a workbook change event ie
before close
Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _
Cancel as Boolean)
Range("A1").Select
End Sub
I am not sure wether this is a general module or not.One of the others will
respom
nd I am sure
<BG






paul
remove nospam for email addy!



"Brisbane Rob" wrote:


Is it possible to, on save and exit, to get the cursor positioned to
cell A1 on every sheet?

Thanks for any help


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=508517

Hi Rob,
the way I've done it is to use the Workbook_Open event rather than the
Workbook_BeforeClose event, which would only work if save was included
in the code. Forcing the workbook to save on closing would make it very
difficult, if not impossible, for a normal user to avoid saving
unwanted changes.

Private Sub Workbook_Open()
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
Sht.Activate
Sht.Range("A1").Select
Next Sht
Worksheets(1).Activate
End Sub

To achieve the same effect with every save just use the same code in
the Workbook_BeforeSave event...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
Sht.Activate
Sht.Range("A1").Select
Next Sht
Worksheets(1).Activate
End Sub

To get the code into place..
Copy
Alt + F11 to enter the VBA editor
ViewProject Explorer to ensure that the Project Explorer is visible
Double click the ThisWorkbook icon (Has the green Excel cross)
Paste code into the white space to the right of the Project Explorer.

Ken Johnson

Brisbane Rob wrote


Is it possible to, on save and exit, to get the cursor positioned to
cell A1 on every sheet?

Thanks for any help

Method I use in Private Sub Workbook_Open():

For Each sh In Worksheets
Application.GoTo sh.Range("A1"), True
Next
Sheets(1).Select

Could also be used in Private Sub Workbook_BeforeSave()
--
David

I like to use the _open event, too.

And another reason to stay away from the _beforesave event is that if the user
is changing something in Z32323 and hits save, that user may not be happy going
to A1.



Ken Johnson wrote:

Hi Rob,
the way I've done it is to use the Workbook_Open event rather than the
Workbook_BeforeClose event, which would only work if save was included
in the code. Forcing the workbook to save on closing would make it very
difficult, if not impossible, for a normal user to avoid saving
unwanted changes.

Private Sub Workbook_Open()
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
Sht.Activate
Sht.Range("A1").Select
Next Sht
Worksheets(1).Activate
End Sub

To achieve the same effect with every save just use the same code in
the Workbook_BeforeSave event...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim Sht As Worksheet
For Each Sht In Me.Worksheets
Sht.Activate
Sht.Range("A1").Select
Next Sht
Worksheets(1).Activate
End Sub

To get the code into place..
Copy
Alt + F11 to enter the VBA editor
ViewProject Explorer to ensure that the Project Explorer is visible
Double click the ThisWorkbook icon (Has the green Excel cross)
Paste code into the white space to the right of the Project Explorer.

Ken Johnson

--

Dave Peterson

Thanks for the help, guys. Much better idea to use 'on open'.


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=508517

Hi Bisbane Bob,
You're welcome.
Thanks for the feedback.
Ken Johnson