Open a worksheet at the top?

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

How can I ensure that my worksheets always open at the top? They all have a lot of vertical scroll
so it would be nice to "start at the begining".

Thanks,

Julie
 
Hi Julie!

Here's a Workbook_BeforeClose subroutine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
Application.ScreenUpdating = True
End Sub

This goes in the ThisWorkbook module

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Julie said:
How can I ensure that my worksheets always open at the top? They
Click to expand...
all have a lot of vertical scroll
 
Julie, if you want this for *all* Worksheets. Right click on the Excel
icon, top left next to "File" and select "View Code" and paste in this

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Goto Range("A1"), True
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Norman said:
Hi Julie!

Here's a Workbook_BeforeClose subroutine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Select
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
Application.ScreenUpdating = True
End Sub

This goes in the ThisWorkbook module
Click to expand...
Hi Norman,

Using your code I received the following error:
Method Select of Object '_Worksheet' failed

Julie
 
I like to put these kinds of things in the workbook_open event. Then each time
I open the workbook, it gets set up the way I like.

Norman used the _BeforeClose routine so that it does it when you're closing the
workbook--but that means you have to worry about whether the workbook should be
saved--maybe the user actually made errors and didn't want to save them.

Anyway, you can't select a sheet if it's hidden. And I'm guessing that you have
a hidden sheet.

This modification of Norman's code worked ok for me. (note the procedure was
changed, too.)

Option Explicit
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If sh.Visible Then
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
End If
Next sh
Application.ScreenUpdating = True
End Sub
 
Back
Top