Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Excel 2000 SP3 (always open displaying a specified

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2000 SP3 (always open displaying a specified

    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.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Excel 2000 SP3 (always open displaying a speci

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2000 SP3 (always open displaying a speci

    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?

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Excel 2000 SP3 (always open displaying a speci

    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!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel 2000 SP3 (always open displaying a speci

    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •