excel - 强制 Excel 用户启用宏

Question

如果用户没有启用宏，有没有办法阻止 Excel 工作簿打开

Answer 1

执行此操作的标准方法是强制用户使用启动屏幕启用宏。

也可以通过写入注册表来修改 VBA 设置(例如使用 VBS - 尽管在公司设置中 GPO 可能会阻止这种情况)。见 http://blogs.msdn.com/b/cristib/archive/2012/02/29/vba-programmatically-enable-access-to-the-vba-object-model-using-macros.aspx有关访问注册表的示例。

闪屏方法

工作簿中除启动画面外的所有工作表均已制作
非常隐藏(只能通过 VBA 或 VBA 编辑器更改)

如果启用了宏:
1)当工作簿打开时，代码会取消隐藏所有这些非常隐藏的工作表
2)当工作簿关闭时，所有这些工作表都会再次变得非常隐藏

如果未启用宏，则用户只能看到启动画面“请启用宏，关闭然后重新打开此文件”

下面列出了包含此技术完整代码的两个链接

Brad Yundt 来自 here at TekTips

代码进入 ThisWorkbook 模块

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet, wsSplash As Worksheet
Application.ScreenUpdating = False
Application.EnableEvents = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVeryHidden
Next ws
Cancel = True
ThisWorkbook.Save
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then ws.Visible = xlSheetVisible
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Open()
Dim ws As Worksheet, wsSplash As Worksheet
Dim Pswd As String
Pswd="myPassword"
Application.ScreenUpdating = False
Set wsSplash = Worksheets("Splash screen")
wsSplash.Visible = xlSheetVisible
For Each ws In ThisWorkbook.Worksheets
   If ws.Name <> "Splash screen" Then 
       If ws.Name="Sheet1" Then
           If InputBox("Please enter your password")=Pswd Then ws.Visible=xlSheetVisible
       Else
           ws.Visible = xlSheetVisible
       End If
   End If
Next ws
wsSplash.Visible = xlSheetVeryHidden
Application.ScreenUpdating = True
End Sub

琼斯克 at VBAeXpress

代码进入 ThisWorkbook 模块

Option Explicit 

Private Sub Workbook_Open() 

    With Application 
         'disable the ESC key
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 

        Call UnhideSheets 

        .ScreenUpdating = True 
         're-enable ESC key
        .EnableCancelKey = xlInterrupt 
    End With 

End Sub 
 '
Private Sub UnhideSheets() 
     '
    Dim Sheet As Object 
     '
    For Each Sheet In Sheets 
        If Not Sheet.Name = "Prompt" Then 
            Sheet.Visible = xlSheetVisible 
        End If 
    Next 
     '
    Sheets("Prompt").Visible = xlSheetVeryHidden 
     '
    Application.Goto Worksheets(1).[A1], True '< Optional
     '
    Set Sheet = Nothing 
    ActiveWorkbook.Saved = True 

End Sub 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    With Application 
        .EnableCancelKey = xlDisabled 
        .ScreenUpdating = False 

        Call HideSheets 

        .ScreenUpdating = True 
        .EnableCancelKey = xlInterrupt 
    End With 
End Sub 

Private Sub HideSheets() 
     '
    Dim Sheet As Object '< Includes worksheets and chartsheets
     '
    With Sheets("Prompt") 
         '
         'the hiding of the sheets constitutes a change that generates
         'an automatic "Save?" prompt, so IF the book has already
         'been saved prior to this point, the next line and the lines
         'relating to .[A100] below bypass the "Save?" dialog...
        If ThisWorkbook.Saved = True Then .[A100] = "Saved" 
         '
        .Visible = xlSheetVisible 
         '
        For Each Sheet In Sheets 
            If Not Sheet.Name = "Prompt" Then 
                Sheet.Visible = xlSheetVeryHidden 
            End If 
        Next 
         '
        If .[A100] = "Saved" Then 
            .[A100].ClearContents 
            ThisWorkbook.Save 
        End If 
         '
        Set Sheet = Nothing 
    End With 
     '
End Sub