how do i make it so that when a sheat is selected either via link or tab, that xlLastCell is selected. the last on the sheet.

how do i make it so that when a sheat is selected either via link or tab,
that xlLastCell is selected. the last on the sheet.

In VBA Editor, create a module. Paste this there

Option Explicit

Dim X As New EventClass

Public Sub Auto_Open()
Set X.App = Application
End Sub



Then, insert a Class Module, rename it to EventClass, and paste this there:

Option Explicit

Public WithEvents App As Application

Private Sub App_SheetActivate(ByVal Sh As Object)
Sheets(Sh.Name).Select
ActiveCell.SpecialCells(xlLastCell).Select
End Sub


It should work (XL2000).



"Daniel" <[email protected]> schreef in bericht
news:[email protected]...
> how do i make it so that when a sheat is selected either via link or tab,
> that xlLastCell is selected. the last on the sheet.
>
>

Hi Daniel,
Don't know how much good it is going to do you, but if you're still
talking to us afterwards I guess it's what worked okay. Besides
lastcell is not always correct, but I would expect you want to see
some rows above, and perhaps have column A cell selected
instead.

You might look at:
A macro to take you to the bottom
and then go up to where your data is, see macros and the toolbar buttons
I created to go with them. That one would be the button with the down arrow.
Macros, some of the short macros used with Toolbar buttons on this page (#macros)
http://www.mvps.org/dmcritchie/excel...ars.htm#macros

The Last cell can be found manually by Ctrl+End and is seldom correct
if you have inserted and deleted rows.

Place the following code into ThisWorkbook,
click on the logo in upper left of file menu on workbook,
then View code, place the following code there.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Cells.SpecialCells(xlLastCell).Activate
End Sub


But you might find this one more useful unless column 1 is empty

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Cells(Rows.Count, 1).End(xlUp).Select
End Sub

or perhaps check the first 3 columns

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim R As Long
'pick the longest of the first 3 columns, and select in the A column
R = Application.WorksheetFunction.Max(Cells(Rows.Count, 1).End(xlUp).row, _
Cells(Rows.Count, 2).End(xlUp).row, _
Cells(Rows.Count, 3).End(xlUp).row)
Cells(R, 1).Select
End Sub

More information on LastCell in
http://www.mvps.org/dmcritchie/excel/lastcell.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Daniel" <[email protected]> wrote in message news:[email protected]...
> how do i make it so that when a sheat is selected either via link or tab,
> that xlLastCell is selected. the last on the sheet.
>
>

Correction to the directions for placing into "ThisWorkbook"
Place the following code into ThisWorkbook,
*right*-click on the logo in upper left of file menu on workbook,
then View code, place the following code there.