vba 如何强制用户在工作表中启用宏？

how to force user to enable macros in the sheet?

提问by user1049518

I have a template with macros to validate the data on clicking the button. The template having only one sheet and no sheets added in that file (It should have one sheet only). Now, I have to do is, when the file is open without macros enabled, i need to display a image, please enable macros other wise no need to display.

我有一个带有宏的模板，用于在单击按钮时验证数据。模板中只有一张纸且该文件中没有添加任何纸（它应该只有一张纸）。现在，我要做的是，当文件在未启用宏的情况下打开时，我需要显示图像，请启用宏，否则无需显示。

Thanks a lot in advance for any help.

非常感谢您的帮助。

回答by Siddharth Rout

how to force user to enable macros in the sheet?

如何强制用户在工作表中启用宏？

The answer to your question is "You Can't"

你的问题的答案是“你不能”

However here is an alternative by Ken Puls which will suit your purpose just fine.

但是，这是 Ken Puls 的替代方案，它很适合您的目的。

Topic: Force users to enable macros in a workbook

主题：强制用户在工作簿中启用宏

Link: http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

链接：http: //www.vbaexpress.com/kb/getarticle.php? kb_id= 379

QUOTE FROM THAT LINK

从那个链接引用

Since there is no way to use a macro to turn on macros, a technique to ensure the user has enabled macros is desirable. This particular method hides all sheets except a "welcome" sheet which tells the user to enable macros, and is enforced every time the workbook is saved. If the user opens the workbook with macros enabled, the sheets will all be unhidden by the macro. The hiding of sheets is also done using Excel VeryHidden property, which means that the sheets cannot be unhidden using Excel's menus. Keep in mind, however, that this only affects this workbook, so a user could use a macro from another workbook to unhide all of your sheets. Chances are, however, if your user is that skilled, they can always get into your file anyway. NOTE: To prevent some event looping issues, this code requires overruling Excel's built in Save events, and also requires replicating Excel's "Workbook has changed, do you want to save" prompts and actions. This code takes care of all of it. It does, however, create a very minor issue when closing the file. If the user trys to quit the application, it will close the workbook, but not Excel. Quitting again will close Excel completely.

由于无法使用宏来打开宏，因此需要一种确保用户已启用宏的技术。此特定方法隐藏除“欢迎”工作表之外的所有工作表，该工作表告诉用户启用宏，并在每次保存工作簿时强制执行。如果用户在启用宏的情况下打开工作簿，宏将取消隐藏所有工作表。工作表的隐藏也是使用 Excel VeryHidden 属性完成的，这意味着无法使用 Excel 的菜单取消隐藏工作表。但是请记住，这只会影响此工作簿，因此用户可以使用另一个工作簿中的宏来取消隐藏所有工作表。但是，如果您的用户技术娴熟，他们总是可以访问您的文件。注意：为了防止某些事件循环问题，此代码需要否决 Excel' s 内置保存事件，还需要复制 Excel 的“工作簿已更改，是否要保存”的提示和操作。这段代码负责所有这些。但是，它在关闭文件时会产生一个非常小的问题。如果用户尝试退出应用程序，它将关闭工作簿，但不会关闭 Excel。再次退出将完全关闭 Excel。

FOLLOWUP

跟进

Before you close the Workbook do the following

在关闭工作簿之前，请执行以下操作

1. Insert a new Row
2. Increase the height of the row
3. Hide the rest of the rows
4. Resize and Move picture at a relevant location

1. 插入新行
2. 增加行高
3. 隐藏其余的行
4. 在相关位置调整图片大小和移动图片

And when you open do the reverse of above

当您打开时，请执行与上述相反的操作

1. Unhide the rest of the rows
2. Delete the 1st Row
3. Decrease the size and Move the picture to a far left location in the sheet

1. 取消隐藏其余行
2. 删除第一行
3. 减小尺寸并将图片移动到工作表中的最左侧位置

Important.

重要的。

When you place the picture on the sheet, right click on the picture and click on 'Format Picture'. In the Format Picture Dialog, select Don't move or size with cellsand uncheck the option which says Print Object

当您将图片放在工作表上时，右键单击图片并单击“设置图片格式”。在 中Format Picture Dialog，选择Don't move or size with cells并取消选中显示的选项Print Object

CODE WHEN CLOSING THE WORKBOOK - Apply the same concept as shown in the above link

关闭工作簿时的代码 - 应用与上述链接中所示相同的概念

Dim shp As Shape

With Sheets("Sheet1")
    .Rows(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Rows(1).RowHeight = 300

    .Rows("2:" & .Rows.Count).EntireRow.Hidden = True
    Set shp = .Shapes("Picture 1")
    With shp
        .Top = Sheets("Sheet1").Range("A1").Top
        .Left = Sheets("Sheet1").Range("A1").Left
        .LockAspectRatio = msoFalse
        .Height = 100
        .Width = 100
    End With
End With

CODE WHEN OPENING THE WORKBOOK - Apply the same concept as shown in the above link

打开工作簿时的代码 - 应用与上述链接中所示相同的概念

Dim shp As Shape

With Sheets("Sheet1")
    .Rows("1:" & .Rows.Count).EntireRow.Hidden = False
    .Rows(1).Delete

    Set shp = .Shapes("Picture 1")
    With shp
        .LockAspectRatio = msoFalse
        .Height = 0
        .Width = 0
        .Top = Sheets("Sheet1").Range("A1").Top
        .Left = Sheets("Sheet1").Range("IV1").Left
    End With
End With

NOTE: In the above code, replace "Sheet1"with the relevant sheet name and replace "Picture 1"with the relevant picture name. I am showing the image in Cell A1. You can amend that to display the picture where ever you want by adjusting .Topand .Leftof the Shape.

注意：在上面的代码中，替换"Sheet1"为相关的工作表名称，替换"Picture 1"为相关的图片名称。我在 Cell 中显示图像A1。您可以修改该显示在任何你想通过调整图像.Top和.Left形状的。

SNAPSHOT IF MACRO IS DISABLED

禁用宏时的快照