使用 VBA 显示工作表

Display a sheet with VBA

提问by Hussain27syed

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).

例如，这本书为 2 张。Sheet1 和 Sheet2。当我运行宏时，Sheet1 应该在屏幕上显示 5 秒，然后 Sheet2 显示 5 秒。然后Shhet1（循环）。

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.

感谢大家的回应。我已经尝试了所有，但无法让它工作。当我运行代码时，工作表显示不会改变。例如，我的工作簿是否有三张表 Sheet1、Sheet2 和 Sheet3，我编写代码以按此顺序显示 sheet1 10 秒、sheet2 10 秒和 sheet3 10 秒，在执行过程中，excel 卡在我开始的工作表上宏 30 秒并显示最终工作表（工作表 3）。不确定这是否是因为代码/应用程序中的某些设置。

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.

无论如何，由于这不起作用，我决定将图表从excel复制到powerpoint（通过vba）并使用ppt显示在仪表板上。我使用了以下链接中提供的代码。

thanks once again for your suggestions

再次感谢您的建议

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

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

回答by Takedasama

You might try this. The macro is an endless loopthrough 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

你可以试试这个。宏是一个无限循环，通过 3 张纸（它可以设置为任何数字），基于计时器设置（此处为2 秒）。您可以在每个工作表加载上通过代码插入一个 msg./validation 框，以询问用户是否要继续洗牌或停止当前选择（这不是那么用户友好）。或者，您可以创建一个新表（即 MacroKeys）并为洗牌设置验证值（是/否）（检查下面的代码）。最后一步是在每个工作表上添加 2 个按钮以创建所谓的 UI 来调用宏 [Shuffle] 和 [Stop]。无论如何你应该使用[Application.OnTime alertTime, "macro name"]函数

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/truewith ' and replace the Selectwith Activate. You'll figure it out! Hope it helps!

笔记！您必须添加一个新工作表并将其命名为 "MacroKeys"。你可以隐藏它，让它不存在。笔记！要添加分配有宏的按钮：按 Alt + N、+SH 并选择一个形状。然后，右键单击形状 > 分配宏（并选择相应的宏）。可选的！使用 Alt + W、+F、+R 挡住顶行，并将按钮高度 = 保持在第 1 行的高度。笔记！它不想隐藏张刚注释掉。可见= FALSE /真用“和替换选择与激活。你会搞清楚的！希望能帮助到你！

回答by Santosh

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

回答by sam092

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

回答by virusrocks

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

激活工作表是通过 Sheet.Activate 方法完成的。尝试这个

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