0 votes
in VBA by Beginner (5 points)
Sub switch()
End Sub


I will display a dashboard with 6 charts on a screen via TV, I put each chart in a different sheet (5 sheets total) and I want to have the display automatically switch from one sheet to the other after approximately 20 seconds. Can you please help me with the OnTime VBA code for this?

1 Answer

+1 vote
by Super Expert (2.9k points)
selected by
Best answer

The Application.Wait VBA method is the easiest way to do this. This macro will loop through each sheet in your workbook and pause 20 seconds after each one.

Sub switch()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
    Application.Wait (Now + TimeValue("00:00:20"))
Next sht
End Sub


by Super Expert (2.9k points)

If you want the code to repeat all day, you can change the macro to this:

Sub switch()
Dim sht As Worksheet
Do 'creates an infinite loop'
    For Each sht In ActiveWorkbook.Sheets 'loops through each sheet'
        sht.Activate 'activates each sheet'
        Application.Wait (Now + TimeValue("00:00:20")) 'waits 20 seconds before going to next sheet'
    Next sht
End Sub

This creates an infinite loop that steps through each sheet every 20 seconds and starts over once it gets through them. I've added some comments for your own curiosity, but you don't have to change anything to make it work.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.