Timer to display worksheet for only 30 seconds

yegarboy · Dec 5th 2010

I have a large workbook with view a lot of only pages. What i would like to do is when a sheet is selected it wil display it for 30 seconds then return to the home sheet. I saw a inactivity timmer but its far to complex for me. Just somthing simple will do fine.

Justin Doward · Dec 5th 2010

Re: Timmer to display worksheet for only 30 seconds

You can use something like this:

Code

Sub showmequick()


Sheets(2).Activate
Application.Wait (Now + TimeValue("00:00:30"))
Sheets(1).Activate


End Sub

yegarboy · Dec 5th 2010

Re: Timmer to display worksheet for only 30 seconds

This is close, But it freezes my userform until time runs out and i cant scroll down the page to read anything.

yegarboy · Dec 5th 2010

Re: Timmer to display worksheet for only 30 seconds

Code

Dim ws As Worksheets


 
 Dim sheetname As String


 
 sheetname = UserForm5.ListBox1.Value


 
 Sheets(sheetname).Select


 
 


 
Application.Wait (Now + TimeValue("00:00:10"))


 
Sheets("Home2").Activate


 
 


 
 


 
 


 
 End

Display More

Justin Doward · Dec 5th 2010

Re: Timmer to display worksheet for only 30 seconds

how about autoscroll?

Code

Sub showmequick()


Sheets(2).Activate


ActiveWindow.ScrollRow = 1


Application.Wait (Now + TimeValue("00:00:02"))


For i = 5 To 150


ActiveWindow.ScrollRow = i
Application.Wait (Now + TimeValue("00:00:01"))


i = i + 2


Next i


Sheets(1).Activate


End Sub

Display More

yegarboy · Dec 6th 2010

Re: Timmer to display worksheet for only 30 seconds

I may have misguided you. Im sorry. I would like the userform to hide and then let the user have free range of the page. Then when time is up return to the home page.

Justin Doward · Dec 6th 2010

Re: Timmer to display worksheet for only 30 seconds

Sorry I cannot see a way to do this. The best I can suggest is to have the macro stamp the Now time to a cell and then compare the time on every event on the page after that. This won't force thirty seconds, but it means if anything is done after thirty seconds you will be returned to the home page?

Like you mentioned there are timers out there but they involve addins.
Hopefully someone else here has a better solution for you.

yegarboy · Dec 6th 2010

Re: Timmer to display worksheet for only 30 seconds

Thanks for your help. Cheers

**Reafidy** · Dec 6th 2010

Re: Timmer to display worksheet for only 30 seconds

Im not sure why you would want to do this but anyway place the following code in a module:

Code

Public dTime As Date
Sub StartTimer()
    dTime = Now + TimeValue("00:00:30")
    Application.OnTime dTime, "ReturnHome"
End Sub
Sub StopTimer()
    On Error Resume Next
        Application.OnTime dTime, "ReturnHome", , False
    On Error GoTo 0
End Sub
Sub ReturnHome()
    On Error Resume Next
        Application.EnableEvents = False
        Sheet1.Activate
        Application.EnableEvents = True
    On Error GoTo 0
End Sub

Display More

In the workbook object:

Code

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = Sheet2.Name Then
        StartTimer
    Else
        StopTimer
    End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
        Application.OnTime dTime, "MyMacro", , False
    On Error GoTo 0
End Sub

Display More

yegarboy · Dec 6th 2010

Re: Timer to display worksheet for only 30 seconds

Thanks for that Reafidy but still not quite right. Let me explain a tad better. When my workbook opens a user logs in. There are many users but only 2 types. "Employee's" and "Managers". If an employee logs in they are brought to a task sheet for data entry. They can not see the sheets they have entered data to. I have installed a "View Only" command button on there task sheet that loads all the relavant sheets with submited data for them to view. but because i dont want to add a return button to every sheet in the book is why i want to allow them x amount of time to view a sheet before returning them to there task sheet. My book is hundreds of sheets. Thanks for all your help

**Reafidy** · Dec 6th 2010

Re: Timer to display worksheet for only 30 seconds

I dont think restricting the time is a good idea, there are all sorts of problems that could arise from this. Why not add the command button "Return Home" to the worksheet at runtime? Then remove it onceit is clicked or the sheet is manually changed.

Timer to display worksheet for only 30 seconds

Participate now!

Share

Users Viewing This Thread