Excel Formatting

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52
Hi everyone:
I am using an excel document with multiple sheets which is also a shared document. I have set it up with one sheet being a menu with hyperlinks.

What I am trying to work out is if anyone knows how I can set the document up so when anyone opens it, it always opens to the sheet that has the menu (ie sheet 1) rather than opening to whatever page the document was last saved on?

Thanks everyone
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
In thisworkbook put in this line.

Code:
Private Sub Workbook_Open()
Sheets("sheet1").Activate

End Sub

Replace "sheet1" with the name of the sheet that should open 1st.
 

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52
joelnichols said:
In thisworkbook put in this line.

Code:
Private Sub Workbook_Open()
Sheets("sheet1").Activate

End Sub

Replace "sheet1" with the name of the sheet that should open 1st.
Thanks Joel, but I'm finding this doesn't work. If I exit out of the viewcode and try it nothing happens. If I click the exit design first it says that macros is not enabled and I'm not sure if or how I am to do this.
Any help would be appreciated.
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
When you are viewing code, on the left side are windows showing the sheets that are open. It will say VBAproject (workbook name). Under that should be all the sheet names in that workbook (sheet1),(sheet2) etc. The last thing will say "thisworkbook". In thisworkbook is where you should paste the code. It is an "open workbook" function so it only runs when the workbook is saved,closed, and re-opened. Hope that helps.
 

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52

ADVERTISEMENT

Hi Joel:
Thanks. I discovered the reason it wasn't working was due to the macros security levels. It is now working perfectly.

NEW QUESTION
The same workbook contains an autofilter on each sheet at the top of the page. Do you have any idea if I can set on workbook opening any active filtering to reset back to no sort.
Thanks
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
Try this.

Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Selection.AutoFilter Field:=1
Next ws
End Sub
 

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52

ADVERTISEMENT

joelnichols said:
Try this.

Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Selection.AutoFilter Field:=1
Next ws
End Sub
I have tried this code and I get an error message when I try to run it. The message is:

Compile Error
Ambiguous name detected: Workbook_Open

Any thoughts?
 

joelnichols

Active Member
Joined
Apr 13, 2004
Messages
384
Is the other workbook_open() code still there or did you combine them? Sounds like you have 2 seperate ones. If so erase the other one and try this. Again this should go in thisworkbook.


Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Select
Selection.AutoFilter Field:=1
Next ws
Sheets("sheet1").Activate
End Sub
 

ozstephan

Board Regular
Joined
Nov 19, 2005
Messages
52
Hi Joel:
When I use this code I get a runtime error 1004 Autofilter method of range class failed.

When I then go to debug it highlights the:
Selection.AutoFilter Field:=1

I have done everything right except I changed "sheet 1" in second last line to 'menu' which is the name of the sheet it should open up on.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,112
Messages
5,570,259
Members
412,314
Latest member
yazanwael
Top