保护和隐藏栏与“非常隐藏"栏相同床单 [英] Protecting and Hiding Columns same as "Very Hidden" Sheets

问题描述

我试图隐藏一些列，以防止用户对其进行任何访问；他们可以编辑工作表的其他部分.

I am trying to hide some columns and prevent the user to have any access to them; while they can edit other parts of the sheet.

我正在寻找的东西是这样的:

What I am seeking is something like:

ActiveWorkbook.Sheets("Name").Visible = xlSheetVeryHidden 

因此用户看不到它们.我知道一个选择是添加另一张纸并将那些列移到那里并隐藏那张纸.但是由于我正在处理相对较大的数据集，并且公司内部具有标准格式，因此我不愿意这样做.

So user cannot see them. I am aware that one option is adding another sheet and move those columns to there and hide that one sheet; but as I am working on a relatively large data-set and it has a standard format within the company, I prefer not to do so.

我已经尝试锁定列并保护工作表并检查除选择锁定单元格，插入和删除行和列(以下代码)所用的框以外的所有框.

I already tried locking the columns and protecting the sheets and checking all the boxes except the ones for selecting locked cells, inserting and deleting both rows and columns (code below).

Function VeryHideColumn(myColumn As Range)

    myColumn.Locked = True
    myColumn.FormulaHidden = True
    myColumn.Hidden = True
    'myColumn.Hidden = xlVeryHidden 'I already tried this

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowSorting:= _
        True, AllowFiltering:=True, AllowUsingPivotTables:=True
    ActiveSheet.EnableSelection = xlUnlockedCells

End Function

这是问题所在:用户仍然可以选择包含这些隐藏和锁定列的范围，然后取消隐藏它们.是否有任何方法或技巧，例如 VeryHidden 用于可用于范围的工作表?

This is the problem: users still can select a range containing these hidden and locked columns and unhide them. Is there any method or trick like VeryHidden for sheets available for ranges?

推荐答案

原始答案:

如果用户不需要格式化列和行，则可以解决此问题:

Original Answer:

If the users don't need to format the columns and rows, then there's a work around for this:

如果 AllowFormattingColumns 和 AllowFormattingRows 为假(如果未声明，则为默认值)，则用户将无法隐藏或取消隐藏它们.

If AllowFormattingColumns and AllowFormattingRows were false (which is default if not stated), then user would not be able to hide or unhide them.

非常隐藏"下方的功能仅基于上述想法的列；

Function below "very hides" only the columns based on above idea;

Function VeryHideColumn(myColumn As Range)
'By M--, April 2017

    myColumn.Locked = True
    myColumn.Hidden = xlVeryHidden
    myColumn.FormulaHidden = True

    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
        AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True

End Function

如果您想非常隐藏行，则将 AllowFormattingRows 设置为false.

If you want to very hide the rows, then set the AllowFormattingRows to false.

这篇关于保护和隐藏栏与“非常隐藏"栏相同床单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！