Excel vba：限制Excel工作表中的编辑区域
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4457672/
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
Excel vba: restrict edit area in excel sheet
提问by Mohamed Saligh
I want to restrict the users to edit range in Ms Excel sheet.
我想限制用户在 Excel 工作表中编辑范围。
Scenario:
| A | B | C | D | E | F | G | H | I | J | ... IV
-------------------------------------------
1 | | | | | | | | | | |
-------------------------------------------
2 | | | | | | | | | | |
-------------------------------------------
3 | | | | | | | | | | |
-------------------------------------------
4 | | | | | | | | | | |
-------------------------------------------
...
65536
In the above spreadsheet user should have access to edit the range Column Ato Column H. Column I to IVusers should not allow to edit any text or anything. No restriction on number of rows.
在上面的电子表格中，用户应该有权将范围编辑Column A为Column H. Column I to IV用户不应允许编辑任何文本或任何内容。行数没有限制。
Thanks :)
谢谢 ：）
回答by Dr. belisarius
In three steps
分三步
1) Select the whole sheet. Format->Lock Cells->Remove locking for all cells. (All cells are initially "locked" by default)
1) 选择整张纸。格式->锁定单元格->取消锁定所有单元格。（默认情况下，所有单元格最初都是“锁定”的）
2) Select your desired locking columns. Format->Lock Cells->Apply Locking (This is declarative, you are not locking nothing, just declaring what you will lock in the next step)
2) 选择您想要的锁定柱。Format->Lock Cells->Apply Locking（这是声明性的，你没有锁定任何东西，只是声明下一步你将锁定什么）
3) Format-> Protect Worksheet. (This trigger the real protection)
3) 格式-> 保护工作表。（这触发了真正的保护）
You are done.
你完成了。
HTH
HTH
回答by sasfrog
To do this programatically, try this in a module (and adapt it to suit your needs):
要以编程方式执行此操作，请在模块中尝试此操作（并对其进行调整以满足您的需要）：
Sub ProtectAToH()
Dim ws as Worksheet
For each ws In ActiveWorkbook.Worksheets
ws.Columns("A:H").Locked = False
ws.Protect Contents:=True, Password:="myPassword"
Next ws
End Sub
回答by Patrick Honorez
Some alternatives to sasfrog and belisarius proposals (just to enrich your options):
sasfrog 和 belisarius 提案的一些替代方案（只是为了丰富您的选择）：
a) you may also just HIDE columns K:IV, and protect the worksheet to prevent unhiding
b) Using the Tools, Protection, "Allow users to edit ranges" option, define range $a:$h as editable without password for users belonging to Everyone group, then protect your sheet. I like that one.
a) 您也可以只隐藏 K:IV 列，并保护工作表以防止取消隐藏
b) 使用工具，保护，“允许用户编辑范围”选项，将范围 $a:$h 定义为无需密码即可编辑属于用户的范围到每个人组，然后保护您的工作表。我喜欢那一个。
Don't forget that any solution involving Excel built in protection will prevent your users from inserting/deleting rows.
不要忘记，任何涉及 Excel 内置保护的解决方案都会阻止您的用户插入/删除行。
c) Using VBA (would not prevent deleting/inserting rows):
c) 使用 VBA（不会阻止删除/插入行）：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("$h:$iv")) Is Nothing) Then
Target.Value = "" 'or something else'
End If
End Sub
回答by Dick Kusleika
ActiveSheet.ScrollArea = "$A:$H"
will restrict what cells the user can select.
将限制用户可以选择的单元格。
回答by thomas398
iDevlop's option c) should be modified to prevent an infinite loop:
iDevlop 的选项 c) 应该修改以防止无限循环：
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("A1:G25")) Is Nothing Then
If Not Target.Cells.Count > 1 Then 'Prevent Error on Row Insert or Delete
If Not Target.Value = "" Then Target.Value = "" 'Prevent infinite loop
End If
End If
End Sub
This will still allow a user to make a multi-cell copy paste into your specified Intersect range. Havent figured that one out.
这仍将允许用户将多单元格复制粘贴到您指定的 Intersect 范围中。还没想通那个。
