Opening a workbook on a set sheet

F

foilprint0

Hi

Wondered if it is possible to set the first workshhet in a workbook as
the opening page every time the workbook is opened, rather than the
last worksheet viewed.
 
F

Frank Kabel

Hi
one way: put the following code in your workbook module:
Private Sub Workbook_Open()
Worksheets("Sheet1").activate

End Sub

change the name of the sheet for your specific workbook
 
N

Norman Harker

Hi Foilprint0


Here's an auto open subroutine you may use to set up your workbook:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Index").Select
Application.ScreenUpdating = True
End Sub

It re-sets all sheets to A1 and opens the workbook at the sheet named
Index.

Workbook_Open subroutines go in the ThisWorkbook module.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

foilprint0

Sorry Iam a bit new to this.
I right click on the tab of the sheet that I want to display first each
time the workbook is opened. select view source
A white box apears in the centre of the screen with
Worksheet in one top box and selectionchange in the other.
below this is the main input box. Is this where I paste that text

Private Sub Workbook_Open()
Worksheets("Index").activate

End Sub


Thankis for any help
 
N

Norman Harker

Hi Foilprint0!

Activate the Visual Basic Editor

Tools > Macro > Visual Basic Editor
Or:
Alt + F11
Or:
Right click the sheet tab > View Code

Then select the workbook in the Project Explorer Window which is the
top left window.
Expand the "tree"
Double click ThisWorkbook
The code window that is now active is where you put the Workbook_Open
code.

You can "top and tail" this event handling subroutine by selecting
Workbook from the left drop down above the code window and selecting
Open from the left drop down. This approach to "topping and tailing"
the workbook level events is recommended as it ensures you have the
right kick off Sub line.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
F

foilprint0

Hi

Thanks for the info but it flagged up an error as soon as i tried t
re-open the workbook.
"Compile Error
Next without For"
any ideas on this?

Private Sub Workbook_Open() THIS WAS HIGHLIGHTED
Application.ScreenUpdating = False
Dim sh As Worksheet
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh THIS WA
HIGHLIGHTED
ThisWorkbook.Sheets("Index").Select
Application.ScreenUpdating = True
End Su
 
F

Frank Kabel

Hi
i think Norman just forgi one line. change his macro to

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim sh As Worksheet
For each sh in Worksheets '<- add this line
Application.Goto Reference:=sh.Range("a1"), Scroll:=True
Next sh
ThisWorkbook.Sheets("Index").Select
Application.ScreenUpdating = True
End Sub

You also have to replace the word "Index" with your specific worksheet
name
 
N

Norman Harker

Hi foilprint0!

Sorry! I was adapting code that does a lot more and deleted some
important line.

It should be:

Private Sub Workbook_Open()
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
ThisWorkbook.Sheets("Index").Select
Application.ScreenUpdating = True
End Sub


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top