Excel 2000 SP3 (always open displaying a specified

Topic

New Reply

I have a workbook with two worksheets; sheet1 & sheet2. I want to always open the workbook displaying cell N78 in sheet1. How can I achieve this?

Reply | Quote

Replies

Activate the Visual Basic Editor (Alt+F11).
Double click ThisWorkbook in the Project Explorer on the left hand side.
Enter or paste the following code in the module that appears:

Private Sub Workbook_Open()
Worksheets(“Sheet1”).Select
Range(“N78”).Select
End Sub

Switch back to Excel (Alt+F11 again) and save the workbook.

Note: make sure macro security is not set to High, for that would disable the code.

Reply | Quote

Thank you Hans; that’s AOK for the workbook file.

However when I save the workbook as a template & open it via File | New it doesn’t behave in the same way. Should the macro work in a template (.XLT) file?

Reply | Quote

Yes, the macro should work in a new workbook based on the template too. What happens if you do the following:
– Open the .xlt file.
– Activate the Visual Basic Editor.
– Select Insert | Module.
– Create the following macro in the new module:

Sub Auto_Open()
Worksheets(“Sheet1”).Select
Range(“N78”).Select
End Sub

– Remove or comment out the Workbook_Open code from ThisWorkbook.
– Save and close the template.
Does this work better?

Reply | Quote

Hans,

Initially, following your further instructions, the behaviour did not change. However I ran the macro (Tools | Macro | Macros | Run) and then saved the template. When I opened the template from File | New it behaved a s required. I think the reason is that the template had been saved with cell J32 highlighted & it needed the highlight to be moved to N78

Thank you for your assi8stance – yet again!

Reply | Quote

Um, if you save the template with Sheet1!N78 selected, you don’t need code to run in a new workbook. (But you do still need the Workbook_Open code for later when the workbook is closed and reopened)

Reply | Quote