revert back to cell A1

[tintin1012000]

Hi all,

I have a graph set up at the top of a sheet
The trouble is that the input data for the chart is in row 1000 +

What I want is after a period of time lets say 60 seconds of no inputs the sheet moves back up to cell a1 therfor my graph will be on display

Can anyone help

 

[anglais428]

to move back to cell A1 you can simply click CTRL + Home at any time, would this suffice?

 

[tintin1012000]

Thanks for your reply anglais428, I am looking for a more automated option, is there any way using VB ?

 

[gsbelbin]

I think your users might find this "feature" is more annoying then useful but I thought I'd have a look just to see if it could be done.

I'll assume you know how to create VBA code etc in this description. If you don't then let me know.

Create a new VBA module and enter the following in it.

Public NextTime As Date
Sub Scroll()
    Cells(1, 1).Select
End Sub

In the code area for the sheet you have the graph and data on enter the following.

Private Sub Worksheet_Change(ByVal Target As Range)
    If NextTime > Now Then
        'Cancel the existing one
        Application.OnTime EarliestTime:=NextTime, Procedure:="Scroll", Schedule:=False
    End If
 
    NextTime = Now + TimeValue("00:01:00")
 
    Application.OnTime NextTime, "Scroll"
End Sub

In the code area for the Workbook itself enter the following. This cancels any outstanding scrolls which would cause the workbook to reopen.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If NextTime > Now Then
        Application.OnTime EarliestTime:=NextTime, Procedure:="Scroll", Schedule:=False
    End If
End Sub

I've tested it for various scenarios and it seems to work fine. Give it a go and let me know how you get on.

Cheers

Gordon

 

[gsbelbin]

Had another think about this. As it stands, if the user makes a change which schedules a scroll in 60 seconds and then moves to another worksheet then the new sheet will scroll to the top ... not really what you want. To overcome this change the Scroll procedure to the following, but change Sheet1 to whatever the name of your sheet is.

Sub Scroll()
    If ActiveSheet.Name = "Sheet1" Then
        Cells(1, 1).Select
    End If
End Sub