• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatting Tabulation - Switching between Worksheet Tabs Automatically

D

DynamiteMom

New Member
I am working on an project that contains several worksheet dashboards within the same workbook. I need a way for the worksheets to automatically change to the next tab after a specified interval (for example switch from sheet1 to sheet2 after 1 minute). This would cause the application to rotate through the worksheet tabs within the workbook displaying 1 tab at a time - kinda like a slideshow affect. This project will be used on a display console for the business to view. Has anyone done this before and had success? I have tried using the timer function in VBA without much success. Any help would be greatly appreciated.
 
DynamiteMum

Try the following macro which must be copied and pasted into a standard module

[pre]
Code: 
Sub TabShow()

Dim i As Integer
Dim Pause As Double

Pause = 3 'Pause delay
Loops = 3 'How many loops do you want to do

For j = 1 To Loops

For i = 1 To Worksheets.Count

Worksheets(i).Select 'Select the next worksheet

x = Timer
While Timer - x < Pause 'This does the pausing
Wend

Next i
Next j

End Sub
[/pre]
 
Thank you for the code. When I copy and paste it into a new module, I get the following error message on the line that states "Worksheets(i).Select": "Run-time error '1004': Select method of Worksheet class failed. I am using MS Office 2007. Should I be specifically referencing the Worksheet?
 
Ok, I figured out that I needed to use the term "Activate" versus "Select". Now it moves throughout the worksheets. The problem now is that I want to have the code only move to the next Visible worksheet. Any hidden worksheets should not be shown as they are the analysis layer of the workbook.


I also noticed that the timer sat for a few seconds on the first sheet, and then navigated through the next sheets very quickly. In the Pause variable, what does the "3" represent? 3 seconds? How do I make it show each sheet for 30 seconds?


Sorry for all the questions, but I am a beginner with VBA and am trying to update our console reporting in a more professional manner.
 
After working through it this afternoon, I figured out that a nested IF statement inside the Loop worked to check to see if the worksheet was active before executing the rest of the code inside the loop. This stopped the Loop from cycling through each of the hidden worksheets and only cycling through the visible ones. I also figured out that the "Pause" variable was in seconds and so I increased the variable from 3 to 30 and it cycles through the visible worksheets nicely.


The only caviat to doing this process is that you cannot navigate away from the spreadsheet while it is running the module. If you do, Excel stops responding and you have to close the application completely. This truly will only work on a machine that is dedicated to running the Excel workbook and nothing else. (No multi-tasking allowed! Unless of course someone knows the work around for that.) :)


Thanks again for getting me started on the road to success. This site is wonderful!
 
The code below allows for Hidden Worksheets

the 2 extra lines are

If Worksheets(i).Visible = "False" Then GoTo 10

&

10:

[pre]
Code: 
Sub TabShow()

Dim i As Integer
Dim Pause As Double

Pause = 3 'Pause delay in seconds
Loops = 3 'How many loops do you want to do

For j = 1 To Loops

For i = 1 To Worksheets.Count

If Worksheets(i).Visible = "False" Then GoTo 10 'Checks for Hidden worksheets and skips if hidden
Worksheets(i).Activate 'Select the next worksheet

x = Timer
While Timer - x < Pause 'This does the pausing
Wend
10: 'This is just a place marker
Next i
Next j

End Sub
[/pre]
 
Thank you for the updated version. Since I am using this code in multiple workbooks and each workbook is on a seperate monitor, I noticed that once I navigate way from the workbook on the second monitor, the code stops working in that workbook. How do I get the code to run if it is not the active workbook?
 
I've never tried to do any Multitasking within Excel and having had a quick play, and it has a few idiosyncrasies.


If you run 1 instance of Excel and have 2 seperate workbooks open within it, only the active one will run a Macro. You can run the macro and change between workbooks, but only the active one is running.


However if you start a second instance of Excel you can load separate files in each and they will both run beside each other quite happily.


I have slightly modified the code so that it will make sure the screen updates properly whilst the macro is running.

[pre]
Code: 
Sub TabShow()

Dim i As Integer
Dim Pause As Double

Pause = 3 'Pause delay in seconds
Loops = 3 'How many loops do you want to do

For j = 1 To Loops

For i = 1 To Worksheets.Count

If Worksheets(i).Visible = "False" Then GoTo 10 'Checks for Hidden worksheets and skips if hidden
Worksheets(i).Activate 'Select the next worksheet

x = Timer
While Timer - x < Pause 'This does the pausing
DoEvents: DoEvents
Wend
DoEvents: DoEvents
10: 'This is just a place marker
Next i
Next j

End Sub
[/pre]
 
Hey. This post has been extremely helpful to me. Thank you!

But I have a small problem. I am running excel 2010 and I was using the code posted above to auto-tab worksheets. I do have a couple of worksheets and according to the code above ...

If Worksheets(i).Visible = "False" Then GoTo 10


When i run thru the code, it doesn't jump to "10" when the worksheet is hidden. So between the last worksheet that is visible and the first worksheet visible there is a considerable lag of time. I have set the pause at 20 secs.


Any help would be appreciated.


Thank you :)
 
You should be able to get rid of the GoTo bit by changing the If function around like so:

Code: 
Sub TabShow()


Dim i As Integer

Dim Pause As Double


Pause = 3 'Pause delay in seconds

Loops = 3 'How many loops do you want to do


For j = 1 To Loops


    For i = 1 To Worksheets.Count


        If Worksheets(i).Visible Then 'Checks for Hidden worksheets and skips if hidden

Worksheets(i).Activate 'Select the next worksheet


        x = Timer

While Timer - x < Pause 'This does the pausing

DoEvents: DoEvents

Wend

DoEvents: DoEvents

End If

Next i

Next j


End Sub
 
Thank you Luke M.

I havea problem though. I was bring in data from an external source (a CSV file) and I had set it up so the file would refresh the data upon opening and then refresh every 30mins.

Running the above mentioned macros is not allowing me to refresh the data on my worksheets. Suggestions please.
 
Oh and also i forgot to mention I run the macros to refresh from the moment the the workbook is opened.


So, in ThisWorkbook I have the following code:

Private Sub Workbook_Open()

Call WorkSheet2.TabShow

End Sub


and in the Worksheet2 i have the following code:

Sub TabShow()


Dim i As Integer

Dim Pause As Double


Pause = 20 'Pause delay in seconds

Loops = 1 'How many loops do you want to do


While Loops


For i = 1 To Worksheets.Count

If Worksheets(i).Visible Then 'Checks for Hidden worksheets and skips if hidden

Worksheets(i).Activate 'Select the next worksheet


x = Timer

While Timer - x < Pause 'This does the pausing

DoEvents: DoEvents

Wend

End If

Next i


Wend


End Sub
 
You must log in or register to reply here.
Back
Top