|
|
|
|
|
|
|
|
|
Excel
|
Select specific range on any worksheet that 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 any worksheet when that worksheet is selected. If you need code that only works for a specific sheet, please see: http://www.vbaexpress.com/kb/submitcode.php?kb_id=330
|
|
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 a sheet. This code works for every sheet in the workbook, and needs to be copied into the ThisWorkbook module.
|
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.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 ThisWorkbook object (in the Microsoft Excel Objects folder).
- Double Click the ThisWorkbook object.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
|
|
Test the code:
|
- On any worksheet (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 in the code is selected
- Return to the Sheet2 and verify that the cell you specified in the code is selected
|
|
Sample File:
|
SendToA1AnySheet.zip 6.16KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 177 times.
|
|
|