如何使用 VBA 在 Excel 中禁用取消隐藏列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23554466/
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 do i disable unhiding columns in Excel using VBA?
提问by PeanutsMonkey
I have a number of columns hidden in Excel and would like to prevent users from using the format control option as well as context menu from unhiding these columns. How can i do so with VBA?
我在 Excel 中隐藏了许多列，并希望防止用户使用格式控制选项以及上下文菜单取消隐藏这些列。我怎样才能用 VBA 做到这一点？
回答by psubsee2003
In terms of actually preventing users from unhiding columns via the menu options, ultimately the options in VBA are limited as VBA doesn't have any special magic. It would be utilizing the exact same functionality as you are using in the Excel application, so the same limitations that you are finding in using the Sheet protection features will be the same in a VBA solution.
在实际阻止用户通过菜单选项取消隐藏列方面，最终 VBA 中的选项是有限的，因为 VBA 没有任何特殊的魔法。它将使用与您在 Excel 应用程序中使用的功能完全相同的功能，因此您在使用工作表保护功能时发现的相同限制将在 VBA 解决方案中相同。
There maybe some VBA-like solutions that could give you close to what you need, but they won't be perfect and would be very easily defeated by simply disabling macros, rendering your security measures completely ineffective.
有可能是一些VBA样的解决方案，可以给你接近你所需要的，但他们不会是完美的，他很轻松地击败了由简单的禁用宏，渲染你的安全措施完全无效。
One option is to rehide the column anytime the selection changes, however this won't prevent user from selecting the entire column and unhiding the row, but as soon as they select a cell, it will be hidden again. you can prevent someone from selecting the cell by locking onlythe cells in this column and protecting the sheet to prevent selection of locked cells. You won't stop anyone from unhiding and seeing the data, but you will prevent them from modifying the data and will make it difficult to see the specific formulas.
一种选择是在选择更改时重新隐藏该列，但这不会阻止用户选择整个列并取消隐藏该行，但是一旦他们选择了一个单元格，它就会再次隐藏。您可以通过仅锁定此列中的单元格并保护工作表以防止选择锁定的单元格来防止某人选择该单元格。您不会阻止任何人取消隐藏和查看数据，但您将阻止他们修改数据并使查看特定公式变得困难。
The code to do that is here and goes in the Worksheetcode section (where you capture worksheet events)
执行此操作的代码位于此处，位于工作表代码部分（您可以在其中捕获工作表事件）
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns("F").EntireColumn.Hidden = True
End Sub
Another option would to disable the appropriate menu options in the Workbook, but this doesn't remove the functionality, just makes it more difficult to access.
另一个选项是禁用工作簿中的相应菜单选项，但这不会删除该功能，只会使其更难以访问。
The code to do that is here and goes in the Workbookcode section (where you capture Workbook-level events)
执行此操作的代码位于此处，位于工作簿代码部分（您可以在其中捕获工作簿级事件）
Private Sub Workbook_BeforeClose(Cancel As Boolean)
//Re-enable in Column Context Menu
Application.CommandBars("Column").Controls("Unhide").Enabled = True
//Re-enable in Ribbon Format menu
Application.CommandBars("Format").Controls("???").Enabled = True
End Sub
Private Sub Workbook_Open()
//Disable in Column Context Menu
Application.CommandBars("Column").Controls("Unhide").Enabled = False
//Disable in Ribbon Format menu
Application.CommandBars("Format").Controls("???").Enabled = True
End Sub
I haven't found the right combination of Menu/Submenu items to disable to menu item from the "Format" menu (hence the "???"), but you would need to do it there as well. Being a macro solution, you are still dependent on someone enabling macros when opening the Workbook. Plus you are physically disabling the menu item so you remove the ability to selection those options for any other sheet in the workbook and any other open workbook, not just the specific columns you want.
我还没有找到菜单/子菜单项的正确组合以禁用“格式”菜单中的菜单项（因此是“???”），但您也需要在那里执行此操作。作为宏解决方案，您在打开工作簿时仍然依赖于启用宏的人。此外，您实际上禁用了菜单项，因此您无法为工作簿中的任何其他工作表和任何其他打开的工作簿选择这些选项，而不仅仅是您想要的特定列。
This is also very version dependent I've found in the past, as menu options have changed in versions of Excel, so what worked in Excel 2007 or 2010, may not work the same in 2013, which means you now need to include a version check in the code and enabled/disable based on the appropriate names for the specific verson.
这也是我过去发现的非常依赖版本，因为菜单选项在 Excel 版本中发生了变化，因此在 Excel 2007 或 2010 中起作用的内容在 2013 年可能不一样，这意味着您现在需要包含一个版本检查代码并根据特定版本的适当名称启用/禁用。
Additionally, if Excel crashes and the BeforeClosecode does not run, you may not get the menu options re-enabled correctly.
此外，如果 Excel 崩溃且BeforeClose代码未运行，您可能无法正确重新启用菜单选项。
Ultimately, the best fail safe is not to hide the columns and to move any sensitive information and formulas into another sheet, which you can then hide via VBA to make it almost invisible from the Excel application.
最终，最好的故障安全措施是不要隐藏列并将任何敏感信息和公式移动到另一个工作表中，然后您可以通过 VBA 将其隐藏，使其在 Excel 应用程序中几乎不可见。
From the Visual Basic Editor, click on a sheet in the Project pane, then from the property pane below, click Visible and change the option to xlSheetVeryHidden
在 Visual Basic 编辑器中，单击“项目”窗格中的工作表，然后从下面的属性窗格中单击“可见”并将选项更改为 xlSheetVeryHidden
The sheet will now be hidden but will not be visible from the Sheet/Unhidemenu, and will only be accessible from the Visual Basic editor (where you have to unhide it again to see it). The best part of this solution is, while it is only accessible from the Visual Basic editor, it is NOTa VBA solution, so will not depend on someone enabling macros to function.
该工作表现在将隐藏但不会从Sheet/Unhide菜单中看到，并且只能从 Visual Basic 编辑器访问（您必须再次取消隐藏它才能看到它）。此解决方案的最佳部分是，虽然它只能从 Visual Basic 编辑器访问，但它不是VBA 解决方案，因此不依赖于启用宏功能的人。
回答by Tim
Once the columns are hidden protect the sheet and ensure that Format Cells/Rows/Columns are not ticked in the Protect Sheet dialog box.
隐藏列后，保护工作表并确保在“保护工作表”对话框中未勾选“设置单元格/行/列格式”。
回答by Hugo
Just hide the headings, and that makes it harder to unhide the columns
只需隐藏标题，这样就更难取消隐藏列
