Setting Sheet Preview time (VBA)

[Luka_LB]

Hello guys,

I've got stucked on this

I have three sheets, which i want to be previewed for a period of time that I have set

something like this



so i can set minutes or seconds and vba will display that specific sheet for that specific time

I'll have on sheet auto refreshing VBA for every second, if this can be helpful


thanks in advance and looking for your replies


PS: I'm a beginner in VBA coding so I' would appreciate if you would explain or give me whole code
thanks again

 

[Luka_LB]

No ideas?

 

[Luka_LB]

up

 

[pjmorris]

Luka,

In a VBA module insert the following code:

Public nSheets As Integer

Sub Recalc()
    Calculate
    Worksheets(nSheets).Select
    nSheets = nSheets Mod Worksheets.Count + 1
    Application.StatusBar = Format(Range("c1").Value, "hh:mm:ss")
    DisplayStatusBar = True
    SetTime
End Sub

Sub SetTime()
    SchedRecalc = Now + TimeValue("00:00:03")
    Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub StopTime()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    On Error GoTo 0
End Sub

then in the ThisWorkBook tab insert this code:

Private Sub Workbook_Activate()
   nSheets = 1
   Recalc
End Sub

Save the spreadsheet and then when you re-open it should show each sheet for 3 seconds - this can be changed be editing the SchedRecalc = Now + TimeValue("00:00:03") line.

Hope this helps.

Regards

Peter

 

[pjmorris]

Luka,

I've had another look at this and think you need to use the following macros, which are a slight amendment from those above (the stoptime macro didn't work as expected):

Public nSheets As Integer

Sub Recalc()
    Calculate
    If nSheets = 0 Then Exit Sub
    Worksheets(nSheets).Select
    nSheets = nSheets Mod Worksheets.Count + 1
    Application.StatusBar = Format(Range("c1").Value, "hh:mm:ss")
    DisplayStatusBar = True
    SetTime
End Sub

Sub SetTime()
    SchedRecalc = Now + TimeValue("00:00:03")
    Application.OnTime SchedRecalc, "Recalc"
End Sub

Sub StopTime()
    On Error Resume Next
    Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    nSheets = 0
    On Error GoTo 0
End Sub

Regards

Peter

 

[pjmorris]

Hi Luka,

Hope this was useful, if it was (or even if it wasn't) it is a courtesy to acknowledge help when offered.

Regards

 

[Luka_LB]

Hi Luka,

Hope this was useful, if it was (or even if it wasn't) it is a courtesy to acknowledge help when offered.

Regards

I know its been a long time... thank you sir! Never meant to be rude or thankless. Hope you are doing very well in this weird times!

 

[pjmorris]

Hi Luka, Appreciated - all well here - just bored with the lack of travel! Hope you and your's are well too.