Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scroll to Top of Sheet, When Sheet is FIRST Activated
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scroll to Top of Sheet, When Sheet is FIRST Activated
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 "ryguy7272" wrote in message ... 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scroll to Top of Sheet, When Sheet is FIRST Activated
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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scroll to Top of Sheet, When Sheet is FIRST Activated
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Mike H" wrote: 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" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bug when Sheet Protection Activated | Excel Programming | |||
Macro to run when sheet activated | Excel Programming | |||
Macro to run when sheet activated | Excel Programming | |||
userform open when sheet activated | Excel Programming | |||
Load VBA on Activated Sheet | Excel Programming |