Excel VBA - 始终在打开时显示工作表

Excel VBA - always show worksheet on open

提问by Aaron Thomas

How can the following conditions be met with VBA code?

VBA代码如何满足以下条件？

1. A particular worksheet is always displayed on open, even if the worbook is opened without enabling macros.
2. A workbook user may save the workbook while working on any worksheet.
3. The save must not interfere with the user - no navigating away to a different sheet, no messageboxes, etc.
4. The regular save functions (Ctrl-S, clicking Save) must remain available and when used must obey the criteria above.

1. 特定的工作表始终显示在打开状态，即使工作簿在未启用宏的情况下打开也是如此。
2. 工作簿用户可以在处理任何工作表时保存工作簿。
3. 保存不得干扰用户 - 不得导航到不同的工作表，没有消息框等。
4. 常规保存功能（Ctrl- S，单击保存）必须保持可用，并且在使用时必须遵守上述标准。

I'd like to avoid the attempted solutions I've listed at the bottom of this question.

我想避免我在这个问题底部列出的尝试解决方案。

Details:
The workbook is created using Office 2007 on a Windows 7 machine. It is an .xlsm workbook with 2 worksheets, "Scheduler" and "Info." Sheet tabs are not visible. Not all users will enabled macros when the workbook is opened.

详细信息：
工作簿是在 Windows 7 计算机上使用 Office 2007 创建的。它是一个 .xlsm 工作簿，包含 2 个工作表，“调度程序”和“信息”。工作表标签不可见。打开工作簿时，并非所有用户都会启用宏。

Upon opening the workbook, a user will only be exposed to one sheet as follows:

打开工作簿后，用户只会看到一张工作表，如下所示：

• "Info" shows up if macros are disabled, and basically tells anyone who opens the workbook that macros need to be enabled for full workbook functionality. If macros are enabled at this point, "Scheduler" is activated.
• "Scheduler" is where data is stored and edited, and is automatically shown if macros are enabled. It is not presented to the user when the workbook is opened without macros enabled.

• 如果宏被禁用，“信息”会显示，并且基本上告诉打开工作簿的任何人需要启用宏才能获得完整的工作簿功能。如果此时启用宏，则激活“调度程序”。
• “调度程序”是存储和编辑数据的地方，如果启用宏，它会自动显示。在未启用宏的情况下打开工作簿时，它不会呈现给用户。

"Info" must show up first thing if the workbook is opened and macros are disabled.

如果打开工作簿并禁用宏，则必须首先显示“信息”。

Attempted Solutions(I'm looking for better solutions!):

尝试的解决方案（我正在寻找更好的解决方案！）：

• Placing code in the Workbook.BeforeSaveevent.This saves with "Info" activated so it shows up when the workbook is opened. However, if the user is in "Scheduler" and not done, I cannot find a way in this event to re-activate "Scheduler" after the save.
• Using Application.OnKeyto remap the Ctrl-sand Ctrl-Skeystrokes.Unfortunately this leaves out the user who saves using the mouse (clicking File...Save or Office Button...Save).
• Checking during every action and if needed activating "Scheduler".In other words, inserting code in something like the Workbook.SheetActivateor .SheetChangeevents to put "Scheduler" back into focus after a save with "Info" activated. This runs VBA code constantly and strikes me as a good way to get the other code in the workbook into trouble.
• Placing code in the Worksheet("Info").Activateevent, to change focus back to "Scheduler". This leads to the result of "Scheduler", not "Info", showing when the workbook is opened, even with macros disabled.

• 在Workbook.BeforeSave事件中放置代码。这会在“信息”激活的情况下保存，以便在打开工作簿时显示。但是，如果用户在“调度程序”中但未完成，则在此事件中我找不到在保存后重新激活“调度程序”的方法。
• 使用Application.OnKey重新映射Ctrl-s和Ctrl-S按键。不幸的是，这遗漏了使用鼠标保存的用户（单击文件...保存或 Office 按钮...保存）。
• 在每个操作期间进行检查，并在需要时激活“调度程序”。换句话说，在激活“信息”的保存后，在诸如Workbook.SheetActivate或.SheetChange事件之类的东西中插入代码以使“调度程序”重新成为焦点。这会不断运行 VBA 代码，这让我觉得是让工作簿中的其他代码陷入困境的好方法。
• 在Worksheet("Info").Activate事件中放置代码，将焦点更改回“调度程序”。这会导致“调度程序”而不是“信息”的结果显示工作簿何时打开，即使禁用了宏。

回答by nunzabar

Will this not work? Updated to handle Saving gracefully

这行不通吗？更新以优雅地处理保存

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Scheduler").Activate
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.Worksheets("Info").Activate
    If (ShouldSaveBeforeClose()) Then
        Me.Save
    Else
        Me.Saved = True ' Prevents Excel Save prompt.
    End If
End Sub

Private Function ShouldSaveBeforeClose() As Boolean
    Dim workbookDirty As Boolean
    workbookDirty = (Not Me.Saved)
    If (Not workbookDirty) Then
        ShouldSaveBeforeClose= False
        Exit Function
    End If

    Dim response As Integer
    response = MsgBox("Save changes to WorkBook?", vbYesNo, "Attention")
    ShouldSaveBeforeClose= (response = VbMsgBoxResult.vbYes)
End Function

回答by Joe

I don't have time to test this out, but you might be able to do this using Application.OnTimein your BeforeSave event handler. Something like:

我没有时间对此进行测试，但是您可以Application.OnTime在 BeforeSave 事件处理程序中使用它来执行此操作。就像是：

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim objActiveSheet
    Set objActiveSheet = Me.ActiveSheet
    If objActiveSheet Is InfoSheet Then Exit Sub
    If Module1.PreviousSheet Is Nothing Then
        Set Module1.PreviousSheet = objActiveSheet
        InfoSheet.Activate
        Application.OnTime Now, "ActivatePreviousSheet"
    End If
End Sub

Then in Module1:

然后在模块 1 中：

Public PreviousSheet As Worksheet

Public Sub ActivatePreviousSheet()
    If Not PreviousSheet Is Nothing Then
        PreviousSheet.Activate
        Set PreviousSheet = Nothing
    End If
End Sub

回答by Reafidy

This problem has been flogged to death in the past, its just hard to find a solution that actually works. Take a look at this code which should do what you need. Basically it shows a splash screen, with all other sheets hidden if the user does not enable macros. It will still save normally if the user clicks save and wont interfere with their work. If they save with there worksheet open it will still show only the splash screen when next opened. Download the sample file below and you can test for yourself, make sure you download the file posted by Reafidy it has over 400 views. If you need it modified further let me know.

这个问题过去一直被鞭打至死，只是很难找到真正有效的解决方案。看一下这段代码，它应该可以满足您的需求。基本上它显示一个启动画面，如果用户没有启用宏，则隐藏所有其他工作表。如果用户点击保存，它仍然会正常保存并且不会干扰他们的工作。如果他们在打开工作表的情况下保存，则下次打开时仍将仅显示启动画面。下载下面的示例文件，您可以自己测试，确保您下载了 Refidy 发布的文件，它有超过 400 次浏览。如果您需要进一步修改，请告诉我。

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    bIsClosing = True 
End Sub 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
    Dim wsArray() As Variant 
    Dim iCnt As Integer 
    Application.ScreenUpdating = 0 

    Splash.Visible = True 

    For Each wsSht In ThisWorkbook.Worksheets 
        If Not wsSht.CodeName = "Splash" Then 
            If wsSht.Visible = True Then 
                iCnt = iCnt + 1: Redim Preserve wsArray(1 To iCnt) 
                wsArray(iCnt) = wsSht.Name 
            End If 
            wsSht.Visible = xlSheetVeryHidden 
        End If 
    Next 

    Application.EnableEvents = 0 
    ThisWorkbook.Save 
    Application.EnableEvents = 1 

    If Not bIsClosing Then 
        For iCnt = 1 To UBound(wsArray) 
            Worksheets(wsArray(iCnt)).Visible = True 
        Next iCnt 
        Splash.Visible = False 
        Cancel = True 
    End If 

    Application.ScreenUpdating = 1 
End Sub 
Private Sub Workbook_Open() 
    Dim wsSht As Worksheet 

    For Each wsSht In ThisWorkbook.Worksheets 
        wsSht.Visible = xlSheetVisible 
    Next wsSht 

    Splash.Visible = xlSheetVeryHidden 

    bIsClosing = False 
End Sub 

A sample file can be found here.

可以在此处找到示例文件。

回答by fan711

How about using a 'proxy workbook'.

如何使用“代理工作簿”。

The 'proxy workbook'

“代理工作簿”

• is the only workbook which is directly opened by the users
• contains the info sheet
• contains VBA to open your 'real workbook' using Workbooks.Open (As I've checked with Workbooks.Open documentation by default it will not add the file name to your recent files history unless you set the AddToMru argument to true)
• if required the VBA code could even make sure that your 'target workbook' is trusted (I found some sample code here)

• 是唯一由用户直接打开的工作簿
• 包含信息表
• 包含使用 Workbooks.Open 打开您的“真实工作簿”的 VBA（正如我已经检查过 Workbooks.Open 文档，默认情况下它不会将文件名添加到您最近的文件历史记录中，除非您将 AddToMru 参数设置为 true）
• 如果需要，VBA 代码甚至可以确保您的“目标工作簿”是可信的（我在这里找到了一些示例代码）

The 'target workbook'

“目标工作簿”

• contains your Schedule and any other sheets
• is only opened if the VBA code in 'proxy workbook' was executed
• can be saved by the user at any time as usual

• 包含您的日程安排和任何其他工作表
• 只有在执行了“代理工作簿”中的 VBA 代码时才会打开
• 可以像往常一样由用户随时保存

I've got no Office 2007 at hand to test this but think it should do.

我手头没有 Office 2007 来测试这个，但我认为它应该可以。

回答by Daniel

Edit 2: Here is a re-write that does not utilize AfterSave. You may need to tweak the dialog created from GetSaveAsFilename according to your needs.

编辑 2：这是一个不使用 AfterSave 的重写。您可能需要根据需要调整从 GetSaveAsFilename 创建的对话框。

This relies on overriding default save behavior and handling the save yourself.

这依赖于覆盖默认保存行为并自己处理保存。

Private actSheet As Worksheet
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    PrepareForSave
    manualSave SaveAsUI
    AfterSave ThisWorkbook.Saved
End Sub
Private Sub PrepareForSave()
    Set actSheet = ThisWorkbook.ActiveSheet
    ThisWorkbook.Sheets("Info").Activate
    hidesheets
End Sub
Private Sub manualSave(ByVal SaveAsUI As Boolean)
    On Error GoTo SaveError 'To catch failed save as
    Application.EnableEvents = False
    If SaveAsUI Then
        If Val(Application.Version) >= 12 Then
            sPathname = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xlsm), *.xlsm")
            If sPathname = False Then 'User hit Cancel
                GoTo CleanUp
            End If
            ThisWorkbook.SaveAs Filename:=sPathname, FileFormat:=52
        Else
            sPathname = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.xls), *.xls")
            If sPathname = False Then
                GoTo CleanUp
            End If
            ThisWorkbook.SaveAs Filename:=sPathname, FileFormat:=xlNormal
        End If
    Else
        ThisWorkbook.Save
    End If
SaveError:
    If Err.Number = 1004 Then
        'Cannot access save location
        'User clicked no to overwrite
        'Or hit cancel
    End If
CleanUp:
    Application.EnableEvents = True
End Sub

Private Sub AfterSave(ByVal bSaved As Boolean)
    showsheets
    If actSheet Is Nothing Then
        ThisWorkbook.Sheets("Scheduler").Activate
    Else
        actSheet.Activate
        Set actSheet = Nothing
    End If
    If bSaved Then
        ThisWorkbook.Saved = True
    End If
End Sub
Private Sub hidesheets()
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Info" Then
            ws.Visible = xlVeryHidden
        End If
    Next
End Sub
Private Sub showsheets()
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = True
    Next
End Sub
Private Sub Workbook_Open()
    AfterSave True
End Sub

The only way to make Infodisplay first without macros enabled is if that is how the workbook was saved. This is most reasonably handled when saving.

在Info没有启用宏的情况下首先显示的唯一方法是，如果这是保存工作簿的方式。这是保存时最合理的处理。

Unless I misunderstood your issue, not using BeforeSaveseems misguided. Just make sure to use AfterSaveas well. Here's an example:

除非我误解了您的问题，否则不使用BeforeSave似乎被误导了。只需确保也使用AfterSave。下面是一个例子：

Private actSheet As Worksheet
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
    showsheets
    actSheet.Activate
    Set actSheet = Nothing
    Thisworkbook.Saved = true 'To prevent save prompt from appearing
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Set actSheet = ThisWorkbook.activeSheet
    ThisWorkbook.Sheets("Info").Activate
    hidesheets
End Sub

Private Sub Workbook_Open()
    showsheets
    ThisWorkbook.Sheets("Scheduler").Activate
End Sub
Private Sub hidesheets()
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Info" Then
            ws.Visible = xlVeryHidden
        End If
    Next
End Sub
Private Sub showsheets()
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = True
    Next
End Sub

The use of the private object actSheet allows the "ActiveSheet" to be reselected after save.

使用私有对象 actSheet 允许在保存后重新选择“ActiveSheet”。

Edit: I noticed you had more requirements in the comments. The code has been updated so that now upon saving, only the Info sheet will be visible, but when opened or after saving, every sheet will reappear.

编辑：我注意到您在评论中有更多要求。代码已更新，现在保存时，只有信息表可见，但打开或保存后，每个表都会重新出现。

This makes it so that any user opening the file without macros will not be able to save with a different sheet activated, or even view the other sheets. That would certainly help motivate them to enable macros!

这使得任何在没有宏的情况下打开文件的用户将无法在激活的不同工作表的情况下进行保存，甚至无法查看其他工作表。这肯定有助于激励他们启用宏！