Switch betwen Excel Sheets so many X seconds?

S

sentinelace

New Member
Joined
Nov 11, 2010
Messages
9
I have a spreadsheet that runs 24/7 monitor and the cells automatically update. I need to add a new sheet to the workbook. My question is, How can I set the spreadsheet to switch between the different sheets every so many seconds?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
This will cycle between Sheet1 and Sheet2 every 2 seconds (change sheet names and cycle time to suit). Press ctrl + pause/break to stop the cycling. I think it would be better to use the OnTime method, but I'm out of time right now.
Code: 
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
If Sh.Name = sh1.Name Then
    Application.Wait Time + TimeSerial(0, 0, 2)
    sh2.Activate
End If
If Sh.Name = sh2.Name Then
    Application.Wait Time + TimeSerial(0, 0, 2)
    sh1.Activate
End If
End Sub
This goes into a ThisWorkbook module.
 
Upvote 0
I am trying to test it but maybe I am putting this in the wrong area. I click on view code in my sheet but there is like 6 modules there. There is one that says thisworkbook. I add it to that but when I hit "Play" to test it just pops up and says which one do you want to run? Tried all of them and I get compile error
 
Upvote 0
Re: Switch between Excel Sheets so many X seconds?

This will loop through all worksheets in the current workbook every five seconds, then when it's done it loops round to do again:-
Code: 
Option Explicit
 
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
 
Public Sub Switch()
 
  Dim ws As Worksheet
 
  Do
    For Each ws In ThisWorkbook.Worksheets
      ws.Activate
      Application.Wait Now() + TimeValue("00:00:05")
      If GetAsyncKeyState(vbKeyShift) Then Exit Sub
      DoEvents
    Next ws
  Loop
 
End Sub
Press SHIFT to interrupt it.
 
Last edited:
Upvote 0
maybe I am doing something wrong. I tried adding the code to a new module and I get:

compile error, cannot find project or library
 
Upvote 0
Odd, it works fine here. Just double-check it?

Otherwise take out both the lines containing the word GetAsyncKeyState and try again. (Press Ctrl-Break to interrupt the display.) Let's test if that's causing it.
 
Upvote 0
Re: Switch between Excel Sheets so many X seconds?

This works Like a Champ ... it took me forever to find this topic to get the right code i needed to cycle through the pages in my work book .... However I have a question ...how can you write this to cycle through only a selection of the pages ... I have some that i do not wish to be opened and viewed as they are only data.
 
Upvote 0
Re: Switch between Excel Sheets so many X seconds?

I like to tell people, "If you can describe what you want in words, a programmer can make the computer do it".

So the question is: which selection of pages do you want to cycle through? How does VBA know which pages you want to display? Is it something in the name of the worksheet? Is it something in the worksheet itself which VBA can use to decide? Or will you supply the programmer with a list of the worksheet names somehow - maybe hard-coded in the VBA or maybe listed in a separate worksheet?
 
Upvote 0
Re: Switch between Excel Sheets so many X seconds?

Ruddles said:
I like to tell people, "If you can describe what you want in words, a programmer can make the computer do it".

So the question is: which selection of pages do you want to cycle through? How does VBA know which pages you want to display? Is it something in the name of the worksheet? Is it something in the worksheet itself which VBA can use to decide? Or will you supply the programmer with a list of the worksheet names somehow - maybe hard-coded in the VBA or maybe listed in a separate worksheet?
Click to expand...





Lets say I have 10 worksheets and all are named differently and i want to display only a "selection" of those worksheets by name ....I used the code below to cycle through the sheets and it works fine... but there is a pause at the end of the cycle which i timed and if you consider the time per page for all my pages both view able and hidden it is pausing for the correct amount it should give if those hidden pages were able to be seen.
 
Upvote 0
Re: Switch between Excel Sheets so many X seconds?

phillipclark said:
Lets say I have 10 worksheets and all are named differently and i want to display only a "selection" of those worksheets by name ....I used the code below to cycle through the sheets and it works fine... but there is a pause at the end of the cycle which i timed and if you consider the time per page for all my pages both view able and hidden it is pausing for the correct amount it should give if those hidden pages were able to be seen.
Click to expand...

The question remains: which selection of pages do you want to cycle through? How is <acronym title="visual basic for applications">VBA</acronym> to know which pages you want to display? Is it something in the name of the worksheet? Is it something in the worksheet itself which <acronym title="visual basic for applications">VBA</acronym> can use to decide? Or will you supply the programmer with a list of the worksheet names?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
VBA code to send an email if a certain IF Criteria is met that loops when the spreadsheet is open
Replies
1
Views
239
joeyc123
J
N
  • Solved
Pulling certain row values if a number is matched
Replies
2
Views
176
Newbie73
N
R
  • Question
Excel Table that automatically links data to second sheet and updates automatically when rows are added
Replies
0
Views
366
rosiez7
R
M
  • Question
Transfer columns from comparable workbook if cell value exists
Replies
2
Views
352
markkuznetsov1
M
C
  • Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
1K
Trebor76
T

Forum statistics

Threads
1,214,982
Messages
6,122,580
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top