VBA Express : Excel - Open book at a given sheet and range regardless of sheet having been renamed

Excel

Open book at a given sheet and range regardless of sheet having been renamed

Ease of Use

Easy

Version tested with

2000

Submitted by:

johnske

Description:

On opening the book in the example, Sheet1 range A1 will be selected. If Sheet1 has been renamed to something else it will still be selected as its codename is still "Sheet1"

Discussion:

There are times when you want to be returned to a given cell on a given sheet each time a book is activated. You, or a user, may also later decide to rename the sheet from 'Sheet1' to 'Home' and maybe even later decide to use 'Main', but that's still the sheet you want the book to open at. But you don't want to change your code every time you rename it, or, for a user to get error messages if they take it on themselves to rename it.

Code:

instructions for use

			Option Explicit 
 
Private Sub Workbook_Open() 
     'open at a sheet (and range) you want regardless
     'of whether or not the sheet has been renamed
     
     'Set the sheet to open (use its codename here)
    Const StartUpSheet = "Sheet1" 
     
     'Set the cell to activate
    Const StartUpCell = "A1" 
     
    Dim N& 
    For N = 1 To Sheets.Count 
        If Sheets(N).CodeName = StartUpSheet Then 
            Sheets(N).Activate 
            Range(StartUpCell).Activate 
            Exit For 
        End If 
    Next 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Tools/Project Explorer
Select the ThisWorkbook module
Copy and paste the code into the Module
Now select File/Close and Return To Microsoft Excel
Dont forget to save your changes...

Test the code:

Select any range on any other sheet and save
Close the book
Re-open the book and you will see Sheet1!A1 has been selected
Re-name Sheet1 to "Home" and move it to another positon
Select another sheet and range and then Save & Exit
Re-open the book and you'll see that "Home" range A1 has been selected

Sample File:

Book3.zip 7.67KB

Approved by mdmackillop

This entry has been viewed 112 times.