使用密码取消保护工作表，而不在宏中显示密码 [英] Unprotect sheet with password, without exposing password in the macro

问题描述

最近我开始在Excel中编写一些Macro。
我有一个受保护的工作表，以及一些允许用户在电子表格中的某一点添加/删除列/行的按钮。

我目前取消保护工作表，执行功能，然后保护工作表。

问题是，受保护的工作表密码是否在宏的纯文本中，以便任何好奇的眼睛请参阅。

如何在表单上保存密码，但同时允许我的宏在执行其功能时取消保护电子表格，而然后重新保护它，而不在宏中键入明文密码？

解决方案

两个选项：

1. 密码保护VBA。 （虽然VBA保护远远没有防范恶意的意图，但它并不比安全性更好）



2. 如果使用VBA设置工作表保护，则可以指定 UserInterfaceOnly：=真。

   
   
   

   sh.Protect密码：=密码，UserInterfaceOnly：= True

   
   
   

   以这种方式设置VBA代码可以修改工作表而不提供密码。由于密码必须先提供一次才能首先应用保护措施，请从单独的工作簿或插件中运行此代码，以保持自己。

I have recently started writing some Macro's in Excel. I have a protected worksheet, and a few buttons that allow the user to add/remove columns/rows at a certain point in the spreadsheet.

I currently unprotect the worksheet, perform the functions and then protect the worksheet.

The problem is, is that the protected sheet password is in plain text in the macro for any curious eyes to see.

How can I have a password on the sheet to protect it, but at the same time, allow my Macro to unprotect the spreadsheet whilst it performs its functions, and then reprotect it, without typing the plaintext password in the macro?

解决方案

Two options:

1. Password protect the VBA. (While VBA protection is far from secure against malicious intent, it is no worse than the security of sheet protection)

2. If you set the worksheet protection using VBA you can specify UserInterfaceOnly:=True.

   sh.Protect Password:="Password", UserInterfaceOnly:=True

   Once set in this way VBA code can modify the sheet without supplying a password. Since the password must be supplied once to apply protection in the first place, run this code from a seperate workbook or addin you keep to yourself.

这篇关于使用密码取消保护工作表，而不在宏中显示密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！