Macro - When opening file return to tab main_Menu

[Johnny Thunder]

Hello,

I am trying to enter a macro for when my excel fie is opened it will go to a specific tab ("Main_Menu") always.

This is so that when my shared filed gets saved in another tab the next user that opens it will go directly to the Main_menu tab.

Thanks in advance for your help!

 

[JSA715]

Put this in your ThisWorkbook module:

Private Sub Workbook_Open()
Worksheets("Main_Menu").Activate
End Sub

 

[Johnny Thunder]

I pasted it into the "This Workbook" and when I save and close the file to try I get the Debugger? It highlights this "Worksheets("Main_Menu").Activate" ??? Did I do something wrong?

 

[erik.van.geit]

Hi,

Are you sure you have a worksheet called "Main_Menu" which is visible?

kind regards,
Erik

EDIT: apparently there is no error even when it is not visible (tested XL2003 and XL2007)

 

[Johnny Thunder]

Yes, i do have a Sheet named "Main_Menu" Maybe I pasted it in wrong? Or in the wrong place?

I went to the object view under VBA Projects>This Workbook> and then entered the macro on the page and saved it?

 

[JSA715]

So this happens when you save and close the file? or when you open it?

 

[Johnny Thunder]

Sorry for the confusion. I entered the macro in, then save and close the workbook;it closes fine. then, when I go to test the macro and open the work book, I enable macro (Security question) I get the debugger, I click debug and I see the line highlighted?

 

[JSA715]

right before you click debug, whats the error message?

 

[Johnny Thunder]

runtime error '9'

Subscript out of range

 

[JSA715]

Generally that is indicative of trying to call something by index that is not available (like calling a worksheet that isn't there). Maybe we could try a test? In the workbook, rename your Main_menu to simply Main and update the code. Save, close and reopen. Let me know how that goes.

-Justin