Scroll to Top of Sheet, When Sheet is FIRST Activated

[ryguy7272]

I am trying to figure out an easy way to scroll to the top of a sheet, when a
user clicks on the sheet. At first I thought something like this (below)
would work:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveSheet.Range("A1").Select...
...or...
Application.GoTo Sheets(1).Range("A1"), True
End Sub

I quickly realized that the Cell A1 is always selected whenever anything in
the sheet is clicked. I want to select Cell A1, or somehow scroll to the top
left of the sheet, when the sheet is clicked on for the first time, from
another sheet, but not each subsequent time the sheet is clicked on (as long
as the sheet has focus). How can this be done?

Thanks,
Ryan---

 

[Bernie Deitrick]

Use the workbook's sheet activate event. Copy the code below into the
codemodule of the ThisWorkbook object.

HTH,
Bernie
MS Excel MVP


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.EnableEvents = False
Sh.Range("A1").Select
Application.EnableEvents = True
End Sub

 

[Mike H]

Hi,

I think you need to set a flag in an unused cell and clear that flag whenb
you deactivate the sheet

Private Sub Worksheet_Deactivate()
Range("iv99").Value = ""
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("IV99").Value = "Done that" Then Exit Sub
Range("A1").Select
Range("IV99").Value = "Done that"
End Sub

Mike

 

[ryguy7272]

That did it, MikeH! Very clever!! There's just one little twist here;
please see code below:

Private Sub Worksheet_Deactivate()
Range("IV1").Value = ""
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call UnprotectSh '(added protection to prevent users from inserting/deleting
rows)
If Range("IV1").Value = "Done that" Then Exit Sub
Range("A1").Select
Range("IV1").Value = "Done that"
Worksheets("Brokers").Cells(1, 5) = ActiveCell.Address
If ActiveCell.Address = "$A$21" Then
Worksheets("Brokers").Select
End If
End Sub

I used to be able to jump to a new sheet, select an account number, and then
jump back to the original sheet. I can't seem to do that now. I tried a few
combinations of things, but nothing really seemed to work. Can you figure it
out?

Thanks,
Ryan---