Excel VBA - 始终显示打开的工作表 [英] Excel VBA - always show worksheet on open

问题描述

如何满足VBA代码的条件？

How can the following conditions be met with VBA code?

1. 一个特定的工作表总是显示在打开的，即使worbook是打开时不启用宏。


2. 工作簿用户可以在任何工作表上工作时保存工作簿。


3. 保存不得干扰用户 - 否导航到不同的工作表，没有消息框等。


4. 常规保存功能（ Ctrl - S ，单击保存）保持可用，使用时必须遵守上述条件。

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.

我想避免在底部列出的尝试解决方案

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

详细信息：

该工作簿是在Windows 7机器上使用Office 2007创建的。它是一个带有2个工作表的.xlsm工作簿，计划程序和信息。工作表选项卡不可见。当打开工作簿时，并不是所有的用户都将启用宏。

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.

打开工作簿后，用户只能按照以下方式公开一张表：

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

• 如果宏被禁用，信息将显示，并且基本上告诉任何人打开工作簿，需要为完整的工作簿功能启用宏。如果此时启用宏，则计划程序将被激活。


• 计划程序是数据存储和编辑的地方，如果启用宏，则会自动显示。在没有启用宏的情况下打开工作簿时，它不会显示给用户。

Info必须显示第一件事，如果工作簿被打开，宏被禁用。

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

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

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

• 将代码放在 Workbook.BeforeSave 事件中。所以它在工作簿打开时显示。但是，如果用户处于计划程序并未完成，则在保存之后，我无法找到重新激活计划程序的方式。


• 使用 Application.OnKey 重新映射 Ctrl - s 和 Ctrl - kbd>击键。不幸的是，这会遗漏使用鼠标保存的用户（单击文件...保存或Office按钮...保存）。


• 检查在每个动作期间，如果需要，激活计划程序。换句话说，插入代码，如$ code> Workbook.SheetActivate 或 .SheetChange 事件将Scheduler重新放入焦点后保存Info激活。这样可以不断地运行VBA代码，并且可以将我的工作簿中的其他代码搞成麻烦。


• 将代码放在 Worksheet（信息）。激活事件，将焦点更改回计划程序。这导致计划程序的结果，而不是信息，即使禁用了宏，也会显示工作簿的打开。

• Placing code in the Workbook.BeforeSave event. 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.OnKey to remap the Ctrl-s and Ctrl-S keystrokes. 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.SheetActivate or .SheetChange events 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").Activate event, 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.

推荐答案

我没有时间测试这个，但是您可以使用BeforeSave事件中的 Application.OnTime 处理程序。一些东西：

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

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

然后在Module1：

Then in Module1:

Public PreviousSheet As Worksheet

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

这篇关于Excel VBA - 始终显示打开的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！