Display a sheet with VBA

Question

I have a set of sheets in a workbook. I have names of the sheets in a particular column on one of the sheets. This workbook will be displayed on a monitor/dashboard. Is there a way i can loop through the sheets and show them one-by-one.

Eg, the book as 2 sheets. Sheet1 and Sheet2. When i run the macro, the Sheet1 should b e visible on the screen for 5 seconds, then Sheet2 for 5 seconds. then Shhet1 (loop).

I am able to add the time delay and loop, however just not able to change display of sheet. I have tried below methods this doesnt work

Application.ScreenUpdating = True

Worksheets("Sheet1").Activate
Worksheets("Sheet1").Select

update :

thanks all for your responses. I have tried all, but could not get it to work. When i run the code, the sheet display doesnt change. Eg, is my workbook has three sheets Sheet1, Sheet2 and Sheet3 and i write the code to display sheet1 for 10 sec, sheet2 for 10 secs and sheet3 for 10 sec in that order, during execution excel is stuck on the sheet from where i started the macro for 30 secs and displays the final sheet (sheet3). Not sure if this is because of some settings in the code/application.

Anyways, since this is not working, i have decided to copy the charts from excel to a powerpoint (by vba) and use the ppt to display on the dashboards. I have used the code provided in the below link.

thanks once again for your suggestions

http://peltiertech.com/Excel/XL_PPT.html

Answer 1

Its always better to use the workbook name followed by sheet name then the range to avoid confusion. This code will work even if multiple workbooks are open.

Sub changeSh()
'Application.ScreenUpdating = True  its true by default

    Dim wkb As Workbook
    Set wkb = ThisWorkbook

    wkb.Sheets("Sheet1").Activate

    Application.Wait Now + TimeSerial(0, 0, 5)

    wkb.Sheets("Sheet2").Activate

End Sub

Answer 2

You might try this. The macro is an endless loop through 3 sheets (it can be set to any number), based on timer set (here 2 sec). You can either insert a msg./validation box via code on each sheet load to ask the user if he wants to keep shuffling or stop on the current selection (this is not that userfriendly). Or, you might create a new sheet (i.e. MacroKeys) and set the validation value (Yes/No) for the shuffling (check the code below). The final step is to add 2 buttons on each worksheet to create a so called UI to call the macros [Shuffle] and [Stop]. Anyway you should use the [Application.OnTime alertTime, "macro name"] function

Sub Show_Sheet1()
Dim MacroKeys As Worksheet: Set MacroKeys = Sheets("MacroKeys")
Dim Sh1 As Worksheet: Set Sh1 = Sheets("Sheet1")
Dim Sh2 As Worksheet: Set Sh2 = Sheets("Sheet2")
Dim Sh3 As Worksheet: Set Sh3 = Sheets("Sheet3")
MacroKeys.Range("A1") = "Yes"
' [...]
Sh1.Visible = True
Sh1.Select
Sh2.Visible = False
Sh3.Visible = False
' [...]
If MacroKeys.Range("A1") = "Yes" Then
alertTime = Now + TimeValue("00:00:02")
Application.OnTime alertTime, "Show_Sheet2"
    End IF
End Sub

Sub Show_Sheet2()
Dim MacroKeys As Worksheet: Set MacroKeys = Sheets("MacroKeys")
Dim Sh1 As Worksheet: Set Sh1 = Sheets("Sheet1")
Dim Sh2 As Worksheet: Set Sh2 = Sheets("Sheet2")
Dim Sh3 As Worksheet: Set Sh3 = Sheets("Sheet3")
MacroKeys.Range("A1") = "Yes"
' [...]
Sh2.Visible = True
Sh2.Select
Sh1.Visible = False
Sh3.Visible = False
' [...]
If MacroKeys.Range("A1") = "Yes" Then
alertTime = Now + TimeValue("00:00:02")
Application.OnTime alertTime, "Show_Sheet3"
End IF
End Sub

Sub Show_Sheet3()
Dim MacroKeys As Worksheet: Set MacroKeys = Sheets("MacroKeys")
Dim Sh1 As Worksheet: Set Sh1 = Sheets("Sheet1")
Dim Sh2 As Worksheet: Set Sh2 = Sheets("Sheet2")
Dim Sh3 As Worksheet: Set Sh3 = Sheets("Sheet3")
MacroKeys.Range("A1") = "Yes"
' [...]
Sh3.Visible = True
Sh3.Select
Sh1.Visible = False
Sh2.Visible = False
' [...]
If MacroKeys.Range("A1") = "Yes" Then
alertTime = Now + TimeValue("00:00:02")
Application.OnTime alertTime, "Show_Sheet1"
End IF
End Sub

Sub Stop_Shuffling()
Dim MacroKeys As Worksheet: Set MacroKeys = Sheets("MacroKeys")
'edit !!!
MacroKeys.Range("A1").Value = "No"
End Sub

Note! You must add a new sheet and name it "MacroKeys". You can hide it and leave it be. Note! To add buttons with macro assigned to them: press Alt + N, +SH and select a shape. Then, right click on the shape > Assign Macro (and select the corresponding macros). Optional! Block the top row using Alt + W, +F, +R and keep the buttons height = to the row 1 height. Note! It you don't want to hide the sheets just comment out the .Visible = false/true with ' and replace the Select with Activate. You'll figure it out! Hope it helps!

Answer 3

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub changeSh()
    While (True)
        Sheets(1).Activate
        'Use your method to add time delay here
        'Sleep 5000
        Sheets(2).Activate
        'Use your method to add time delay here
        'Sleep 5000
    Wend
End Sub

Answer 4

Activating sheet is done BY Sheet.Activate method. Try this

Application.ScreenUpdating = True
Worksheets("Sheet1").Activate Worksheets("Sheet1").Select
Worksheets("Sheet1").Activate Worksheets("Sheet1").Activate