VBA Express : Excel - Select specific range when specific worksheet is activated

Excel

Select specific range when specific worksheet is activated

Ease of Use

Easy

Version tested with

2003

Submitted by:

Ken Puls

Description:

This macro will select the designated cell (or cells) on a worksheet when that worksheet is selected. If you need code that works for any (and every) sheet, please see: http://www.vbaexpress.com/kb/submitcode.php?kb_id=335

Discussion:

There are times when you would want to force a user to be returned to the a specific cell each time they activate a sheet. For example, say the worksheets contain thousands of rows of data, and you want the person returned to the top of the list each time they open that sheet. This code works only on a specific sheet, and needs to be copied into the Sheet module for the sheet that you want it to work on.

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_Activate() 
     'Update A1 to the range you want to select
    Range("A1").Select 
End Sub

How to use:

Copy above code.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Locate the desired file on left (in bold).
Drill down (if necessary) until you find the Sheet that you want the code to work on (in the Microsoft Excel Objects folder).
Double Click the Sheet object.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.

Test the code:

On the sheet to which you added the code (we'll assume Sheet1), select a cell other than that you specified in the code
Change to a different sheet (say Sheet2) and select a different cell there as well
Return to Sheet1 and verify that the cell you specified is selected
Return to the Sheet2 and verify that the cell you selected previously is still selected (hasn't moved)

Sample File:

SendToA1SpecificSheet.zip 5.97KB

Approved by mdmackillop

This entry has been viewed 218 times.