How do we control what part of a sheet Excel opens to?

We are working with Excel 2000. We have one large sheet that is our calender for everyone at work. It is about as wide as one screen, but it is several screenfulls long (downward). How can we save the file so that it opens to the correct position on the sheet. It doesn't seem to matter if we are adding data in the february area of the sheet and then save it. The sheet can still open positioned around november.How do we control what part of a sheet Excel opens to?
My experience with Excel is that it opens where it was when last saved.





There are three subroutines in the ThisWorkbook code where that can be affected.





BeforeSave can reposition the screen. Normally you would expect users to notice that, but it would not be seen if the file were being saved from BeforeClose. BeforeClose is the second place where unnoticed repositioning can occur.





If the file is saved during the close process and any team members are unaware of that it greatly increases the possibility that the unintended repositioning is being caused by team members closing the workbook while viewing November.





The third place is Workbook_Open. This is also a place where you can establish control of where the screen is positioned on open regardless of other unintended actions as indicated by two examples below.





EXAMPLE 1.





Requirement: Always open the workbook in the only worksheet with the Cell in Column A and a designated Row in the top left corner of the display.





1. Create a public macro to set the value of Cell A1 to the number of the currently selected Row. Set the font color of Cell A1 to white so that it will not print and only show its value when selected.





2. In the Workbook_Open subroutine include two statements at the end of the routine:





Private Sub Workbook_Open()


Cells(Cells(1, 1) + 200, 1).Select


Cells(Application. WorksheetFunction. Max (Cells(1, 1), 1), 1).Select


End Sub





When the workbook opens these two statements refer to the value in Cell A1 to select a cell well below the bottom of the desired page view and then select the cell in Column A and the designated row.





The Max function is used to select row 1 in the case where Cell A1 is blank or negative.





To handle a potential value, greater than the number of rows in the worksheet, in A1 you could use a Min function in the first statement.





EXAMPLE 2.





Another simple possibility if you always want to open at a point some set number of row from the bottom of the work sheet data doesn't need a special macro or any maintenance:





Private Sub Workbook_Open()


Sheets(';Sheet1';).Activate


ActiveSheet.UsedRange.Cells _


(ActiveSheet.UsedRange. Cells.Count).Select


Cells(Selection.Row - 30, 1).Select


End Sub





Your opening point will move down as you add data to the bottom of the worksheet.





If you have another scenario you would like me to address, contact me by email or modify your question.





NOTE: Spaces have been inserted in the code above to preserve readability in the Answers format. They should be automaticaly removed by the VBAProject editor.How do we control what part of a sheet Excel opens to?
You can do some Hyperlinks to do that


Create Hyperlinks (Static Hyperlinks) in a new sheet, the the user can select any of them





OR





You can create dynamic hyperlink using some functions like HYPERLINK





So, if you want to go to the first cell that says the month name that is as the month that the file is opened at, use this in a new sheet





=HYPERLINK( INDIRECT( ';Sheet1!$C$1';%26amp;MATCH( TEXT( MONTH( TODAY()),';mmm';), Sheet1$C:$C,0))-1, ';Goto this month';)





This assumes:


1- You have the list on month names (Jan, Feb, etc) in column C in Sheet1


2- You click on this link ons you open the file





If you want that automatically, then you need a macro





Reply me with more details to do the macro for you





Thanks





VBAXLMan is here to feed your Excel needs