vba 如何保护工作表名称而不是整个工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8519732/
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
How to Protect the sheet name not the entire sheet?
提问by user1049518
I need to protect the sheet name by preventing
我需要通过防止来保护工作表名称
- any change to the sheet name, or
- the sheet being deleted.
- 对工作表名称的任何更改，或
- 被删除的工作表。
This must be done withoutprotecting the entire sheet using the Protect Sheetor Protect Workbookoptions.
这必须在不使用Protect Sheet或Protect Workbook选项保护整个工作表的情况下完成。
Is there any way to do this with VBA?
有没有办法用 VBA 做到这一点？
采纳答案by brettdj
- Right click the sheet tab that you wish to protect
- View Code
- Copy and paste in the code below
- 右键单击要保护的工作表标签
- 查看代码
- 复制并粘贴下面的代码
This code disables the delete control on the sheet (but not right click on cell) menu when the sheet is activated. The control is enabled when the sheet is de-activated
当工作表被激活时，此代码禁用工作表上的删除控件（但不是右键单击单元格）菜单。禁用工作表时启用控件
The code will also name the sheet "NameOfSheet" when the sheet is de-activated. This is a workaround to prevent the sheet being renamed
当工作表被停用时，代码还将命名工作表“NameOfSheet”。这是防止工作表被重命名的解决方法
Private Sub Worksheet_Activate()
Application.CommandBars.FindControl(ID:=847).Enabled = False
End Sub
Private Sub Worksheet_Deactivate()
Application.CommandBars.FindControl(ID:=847).Enabled = True
Me.Name = "NameOfSheet"
End Sub
回答by Juliusz
I don't think you can. What you can do, you can make a worksheet a very hidden one (accessible only from VBA) and in case of the deleted sheet, you can copy it and make a copy visible.
我不认为你可以。您可以做什么，您可以将工作表设置为非常隐藏的工作表（只能从 VBA 访问），如果工作表已删除，您可以复制它并使副本可见。
回答by Robert Ilbrink
Would this approach work?
这种方法行得通吗？
- Select all cells in the sheet, then UN-lock all cells with "Lock Cells" (yellow background of padlock turns white).
- Write the name of the sheet in a (fixed or named) cell, then lock this cell ONLY ("Lock Cells", padlock background turns yellow).
Then Protect workbook, but allow every action, except the first one "Select Locked Cells".
The user can do everything except selecting the cell with the sheetname (and delete rows/columns).
Now write a VBA to compare the actual sheetname with the data in the protected named cell (or fixed reference e.g. A1).
- Run this script either on every change (probably too much) or at least on close of the workbook.
- As long as the sheetname is always in the same cell (e.g. A1), you can then loop through all sheets, compare their name with the data in cell A1 and correct the sheet name if required.
- 选择工作表中的所有单元格，然后使用“锁定单元格”解锁所有单元格（挂锁的黄色背景变为白色）。
- 在（固定或命名的）单元格中写入工作表的名称，然后仅锁定该单元格（“锁定单元格”，挂锁背景变为黄色）。
然后保护工作簿，但允许所有操作，除了第一个“选择锁定的单元格”。
除了选择带有工作表名称的单元格（并删除行/列）之外，用户可以做任何事情。
现在编写一个 VBA 将实际工作表名称与受保护的命名单元格（或固定引用，例如 A1）中的数据进行比较。
- 在每次更改（可能太多）或至少在工作簿关闭时运行此脚本。
- 只要工作表名称始终在同一个单元格中（例如 A1），您就可以遍历所有工作表，将它们的名称与单元格 A1 中的数据进行比较，并在需要时更正工作表名称。
