Excel Formatting

[ozstephan]

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

 

[joelnichols]

In thisworkbook put in this line.

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

End Sub

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

 

[ozstephan]

In thisworkbook put in this line.

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]

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]

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]

Try this.

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]

Try this.

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]

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]

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.

 

[ozstephan]

BTW, thanks for your help with this. Have a great Christmas.