I found a macro on the web to protect a worksheet with a password. It works fine, but when I save the file I get the message: run-time error '9': subscription out of range. I have never programmed or used visual basic before and could use some help . Thank you
我在网上找到一个宏来保护工作表的密码。它运行良好，但当我保存文件时，我得到消息:运行时错误'9':订阅超出范围。我以前从来没有编程或使用过visual basic，可以使用一些帮助。谢谢你！
The macro is:
宏:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1:Protect the sheet with a password
Sheets("Sheet1").protect Password:="btfd"
'Step 2: Save the workbook
ActiveWorkbook.Save
End Sub
3 个解决方案
#1
3
"Subscript out of range" indicates that you've tried to access an element from a collection that doesn't exist. Is there a "Sheet1" in your workbook? If not, you'll need to change that to the name of the worksheet you want to protect.
“超出范围的下标”表示您试图从一个不存在的集合中访问元素。你的工作簿上有“Sheet1”吗?如果没有，则需要将其更改为要保护的工作表的名称。
#2
2
Why are you using a macro? Excel has Password Protection built-in. When you select File/Save As... there should be a Tools button by the Save button, click it then "General Options" where you can enter a "Password to Open" and a "Password to Modify".
为什么要使用宏?Excel内置了密码保护功能。当您选择File/Save As…保存按钮应该有一个工具按钮，然后点击“通用选项”，你可以输入“打开密码”和“修改密码”。
#3
0
When you get the error message, you have the option to click on "Debug": this will lead you to the line where the error occurred. The Dark Canuck seems to be right, and I guess the error occurs on the line:
当您获得错误消息时，您可以选择单击“Debug”:这将引导您到达错误发生的位置。黑暗的Canuck似乎是对的，我猜错误发生在线路上:
Sheets("Sheet1").protect Password:="btfd"
because most probably the "Sheet1" does not exist. However, if you say "It works fine, but when I save the file I get the message: run-time error '9': subscription out of range" it makes me think the error occurs on the second line:
因为很可能“Sheet1”不存在。但是，如果您说“它运行良好，但是当我保存文件时，我得到了消息:运行时错误‘9’:订阅超出范围”，它使我认为错误发生在第二行:
ActiveWorkbook.Save
Could you please check this by pressing the Debug button first? And most important, as Gordon Bell says, why are you using a macro to protect a workbook?
你可以先按一下Debug按钮来检查一下吗?最重要的是，正如Gordon Bell说的，为什么要使用宏来保护工作簿?
#1
3
"Subscript out of range" indicates that you've tried to access an element from a collection that doesn't exist. Is there a "Sheet1" in your workbook? If not, you'll need to change that to the name of the worksheet you want to protect.
“超出范围的下标”表示您试图从一个不存在的集合中访问元素。你的工作簿上有“Sheet1”吗?如果没有，则需要将其更改为要保护的工作表的名称。
#2
2
Why are you using a macro? Excel has Password Protection built-in. When you select File/Save As... there should be a Tools button by the Save button, click it then "General Options" where you can enter a "Password to Open" and a "Password to Modify".
为什么要使用宏?Excel内置了密码保护功能。当您选择File/Save As…保存按钮应该有一个工具按钮，然后点击“通用选项”，你可以输入“打开密码”和“修改密码”。
#3
0
When you get the error message, you have the option to click on "Debug": this will lead you to the line where the error occurred. The Dark Canuck seems to be right, and I guess the error occurs on the line:
当您获得错误消息时，您可以选择单击“Debug”:这将引导您到达错误发生的位置。黑暗的Canuck似乎是对的，我猜错误发生在线路上:
Sheets("Sheet1").protect Password:="btfd"
because most probably the "Sheet1" does not exist. However, if you say "It works fine, but when I save the file I get the message: run-time error '9': subscription out of range" it makes me think the error occurs on the second line:
因为很可能“Sheet1”不存在。但是，如果您说“它运行良好，但是当我保存文件时，我得到了消息:运行时错误‘9’:订阅超出范围”，它使我认为错误发生在第二行:
ActiveWorkbook.Save
Could you please check this by pressing the Debug button first? And most important, as Gordon Bell says, why are you using a macro to protect a workbook?
你可以先按一下Debug按钮来检查一下吗?最重要的是，正如Gordon Bell说的，为什么要使用宏来保护工作簿?