|
|
|
|
|
|
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()
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.
|
|