Excel

Ensure that cell A1 on a specific sheet is selected when workbook is opened

Ease of Use

Easy

Version tested with

2003 

Submitted by:

Ken Puls

Description:

This macro automatically activates the specified sheet and selects cell A1 whenever the workbook is opened. It can also VERY easily be updated to be a different range. 

Discussion:

Sometimes you may want to ensure that your users always open your workbook at a specific cell in a specific sheet. This code will allow you to do that. By default, this code selects cell A1 on a worksheet called "Home". 

Code:

instructions for use 

			
Option Explicit 
 
Private Sub Workbook_Open() 
     'Change "Home" to the name of the worksheet you want to
     'opened when opening
    With Worksheets("Home") 
        .Activate 
         'Change A1 to the cell that you want selected
        .Range("A1").Select 
    End With 
End Sub

How to use:

  1. Copy above code.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Locate the desired file on left (in bold).
  5. Drill down (if necessary) until you find the file's ThisWorkbook object (in the Microsoft Excel Objects folder).
  6. Double Click the ThisWorkbook object.
  7. Paste code into the right pane.
  8. Press Alt + Q to close the VBE.
  9. Save workbook before any other changes.
 

Test the code:

  1. Make sure you select any cell other than A1 (or whichever you specified), or are on a different sheet
  2. Save and close the workbook
  3. Open the workbook and confirm that you are now in cell A1 on the sheet you specified in the code
 

Sample File:

GoHome.zip 6.33KB 

Approved by mdmackillop


This entry has been viewed 157 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express