vba 输入指定文本时锁定工作表的所有单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3841330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Lock all the cells of a worksheet when specified text entered
提问by guest1
I have an Excel sheet which has fields to enter data.
我有一个 Excel 工作表，其中包含用于输入数据的字段。
Let's say the total fields is 20. Ten of them are locked by default whenever the user opens the workbook. Now, one of the fields is asking the user to enter a password. If the password was "AAA", then five fields (of the ten locked ones) will be unlocked . If the user inputs a password as "BBB", then all the cells of the worksheet will be read-only and locked.
假设总共有 20 个字段。默认情况下，每当用户打开工作簿时，其中 10 个字段都会被锁定。现在，其中一个字段是要求用户输入密码。如果密码是“AAA”，那么（十个锁定的）五个字段将被解锁。如果用户输入密码为“BBB”，则工作表的所有单元格都将是只读的并被锁定。
I am focusing on the case when the user inputs "BBB". I tried this code:
我关注的是用户输入“BBB”的情况。我试过这个代码：
if Range("Password").value="BBB" then
cells.select
selection.locked=true
end if
It gives me an error as " Overflow".
它给了我一个“溢出”的错误。
回答by CaBieberach
If Range("Password").Value = "BBB" Then
ActiveSheet.UsedRange.Locked = True
End If
回答by Siddharth Rout
It gives me an error as " Overflow".
它给了我一个“溢出”的错误。
I doubt you should get an overflow error with this code. An overflow may occur if you use something like Cells.Countin Excel 2007 onwards. CountLargewas introduced just because Cells.Countreturns an Integervalue which errors out in Excel 2007 because of increased rows/columns. Cells.CountLargereturns a Longvalue.
我怀疑您是否应该收到此代码的溢出错误。如果您使用类似于Cells.CountExcel 2007 中的内容，则可能会发生溢出。CountLarge引入只是因为Cells.Count返回一个Integer值，该值由于行/列增加而在 Excel 2007 中出错。Cells.CountLarge返回一个Long值。
Now back to your query.
现在回到您的查询。
You do not need to SELECTall cells. In fact you should avoid the use of Select. You may want to see How to avoid using Select in Excel VBA
您不需要选择所有单元格。事实上，您应该避免使用Select. 您可能想查看如何避免在 Excel VBA 中使用 Select
Also locking all the cells of the worksheet will be ineffective if you do not protect your worksheet. You can change your code to
如果您不保护工作表，锁定工作表的所有单元格也将无效。您可以将代码更改为
If Range("Password").Value = "BBB" Then
With ActiveSheet
.Cells.Locked = True
.Protect "Password"
End With
End If
And if you do not want to use Activesheetthen use something like this
如果你不想使用Activesheet然后使用这样的东西
Option Explicit
Sub Sample()
Dim ws As Worksheet
'~~> Change this to the relevant sheet
Set ws = Sheet1
With ws
If .Range("Password").Value = "BBB" Then
.Cells.Locked = True
.Protect "Password"
'~~> To unprotect, uncomment the below
'.UnProtect "Password"
End If
End With
End Sub
