时间戳

首页 > 解决方案 > VBA 出现问题，旨在防止在保存时编辑具有值的单元格

excel - VBA 出现问题，旨在防止在保存时编辑具有值的单元格

问题描述

我正在处理多个用户将要编辑的电子表格中的表格。我们遇到了人们意外粘贴其他条目或将半行向上移动几列的问题。我想保护每个包含值的单元格，或者允许编辑每个没有值的单元格。

我尝试使整个表格可编辑，然后锁定具有值的单元格。这似乎什么也没做，因为无论如何我都可以编辑值。

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim NewEditRange As Range
    Dim ThisSheet As Worksheet
    Dim DataTable As Range
    Dim CurrentEditRange As AllowEditRange
    Dim CurrentCell As Range
    Set ThisSheet = ActiveWorkbook.Worksheets("Data")
    Set DataTable = ThisSheet.Range("Table1")
    Set NewEditRange = ThisSheet.Range("$A$1")
    ThisSheet.Unprotect
    For Each CurrentEditRange In ThisSheet.Protection.AllowEditRanges
        CurrentEditRange.Delete
    Next
    For Each CurrentCell In DataTable
        If CurrentCell.Value = "" Then
            Set NewEditRange = Application.Union(NewEditRange, CurrentCell)
        End If
        If CurrentCell.Value = "0" Then
            Set NewEditRange = Application.Union(NewEditRange, CurrentCell)
        End If
    Next
    ThisSheet.Protection.AllowEditRanges.Add Title:="New Edit Range", Range:=NewEditRange
    ThisSheet.Protect
End Sub

我希望这可以解锁工作表，删除所有 AllowEditRange 对象，然后将表中的每个空白单元格添加到 NewEditRange，然后创建一个包含 NewEditRange 的新 AllowEditRange，然后保护工作表。相反，它通过第 14 行左右的单元格创建了一个 AllowEditRange，我仍然可以编辑不在该范围内的单元格。

标签： excelvba

解决方案

Okay, so it turns out the problem is that you can't protect cells in tables. Period. If you are interested in them adding this feature, go to https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows and vote.

推荐阅读