MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to open specific sheet at book open?


Posted by Russ Posey on September 27, 2000 6:21 AM

My client has a workbook with several sheets. Upon opening the XLS file, they would like the book to open to a certain sheet. The sheet they want is variable: it could change by day, who clicked this shared XLS, etc. Is this possible (perhaps in a stattrtup macro)?

My initial thought (I have a VB background) would be to include a command line parameter in the shortcut to the file, read it and go to that sheet upon opening the workbook. Is this possible?

Thanks


Posted by Ryan on September 27, 2000 6:33 AM

Russ,

This is possible, you will just need to include all the variables in the code. Here is an example:

Private Sub Workbook_Open()

Select Case Application.UserName

Case "Bob"
Sheets(1).Select
Case "Steve"
Sheets(2).Select
End Select

And So on
End Sub

This procedure goes in the ThisWorkbook Module. Hope this gets you in pointed in the right direction.

Ryan

Posted by Russ Posey on September 27, 2000 6:48 AM

I should add that the ultimate goal is to access these particular sheets via our intranet. Their dream scenario is several links all pointing to the same XLS but different sheets. This is all in Excel 97.

Thanks again.