Automatically go to Worksheet upon opening

[Toonloon]

Hi All...

Upon opening, I want my Workbook to automatically go to a worksheet titled "Main Menu".

This workbook is shared out to multiple users.

The code i currently have in "ThisWorkbook" object is:
Private Sub Workbook_Open()
Sheets("Main Menu").Activate
Sheets("Main Menu").Range("A1").Activate
End Sub

This works, however, users are intermittently getting the error
"Run Time Error '1004'
Select method of Range class failed"

Any ideas/suggestions ?

Thanks a million.

 

[Michael M]

Hi
One line is all you need

Private Sub Workbook_Open()
Sheets("Main Menu").Range("A1").Activate
End Sub

 

[VoG]

Hi
One line is all you need

Private Sub Workbook_Open()
Sheets("Main Menu").Range("A1").Activate
End Sub

I think that will fail if Main Menu is not already the active sheet.

Try

Private Sub Workbook_Open()
Application.Goto Sheets("Main Menu").Range("A1"), True
End Sub

 

[Toonloon]

Thanks for the replies lads,

Michael... i tried a lot of different permutations of the code, 1 line, 2 lines etc... but still got the error

However.....

I have not tried VoG's code..... so... here's fingers crossed....i probably won't find out for a while

Thanks a mill to both of ye

 

[Toonloon]

Ah Well...

Just tried it out and got the error again.

Would the fact that the Workbook is shared out cause the error ?

 

[VoG]

Would the fact that the Workbook is shared out cause the error ?

Almost certainly. You could avoid the error like this

Private Sub Workbook_Open()
On Error Resume Next
Application.Goto Sheets("Main Menu").Range("A1"), True
On Error GoTo 0
End Sub

 

[kidwispa]

Sorry to jump on the back of this thread but thought that was better than starting a new thread as it relates to the same thing. I've used the code suggested by VoG above and it works like a treat, however upon opening I would also like to run a macro. Can you please expand on the code suggested so I can incorporate this too? The macro is called "PivotSec1".

Thanks

 

[VoG]

Try

Private Sub Workbook_Open()
Application.Goto Sheets("Main Menu").Range("A1"), True
Call PivotSec1
End Sub

 

[kidwispa]

Hi VoG,

Thanks for speedy response, however I still get an error message. Basically I've got a pivot chart on the page that I want to open, however the page is password protected and I get an error message come up that says:

"That command cannot be performed while a protected sheet contains another PivotTable report based on the same source data"?

The macro I created was to unprotect the sheet, select the "use pivottable reports" option (as for some reason this doesn't save) then reprotect the sheet.

Any ideas how I can bypass this error message?

 

[Toonloon]

VoG...you're a LEGEND
All working ok

Thanks a mill