主页 / user-10068423

SamB's questions

Martin Hope
SamB
Asked: 2025-02-19 11:55:39 +0800 CST
  • 5

我在受保护的工作表中有一个 Excel 表。我正在尝试执行以下步骤：

  1. 添加一个名为“决策”的新列
  2. 解锁“决策”列内的单元格
  3. 根据另一列“已售”中的值过滤表格（如果某一行显示错误，则保持其可见并隐藏所有其他行）
  4. 对所有可见单元格应用数据验证，仅允许 2 个值“删除”或“分类”

我有以下代码。当我运行它时，我没有收到任何错误，但预期单元格中没有验证，并且单元格未解锁。另外，我认为解锁可能应该在过滤之后进行，但请考虑一下。

    Private Sub HandleErrors()

    Dim ws, wsList As Worksheet
    Dim importTable As ListObject
    Dim DecisionColumn As ListColumn
    Dim listRange, valRange, cell As Range
    Dim listOptions As String

    Set ws = Worksheets("Sales Import")
    Set importTable = ws.ListObjects("Table_SalesImport")
    Set wsList = ThisWorkbook.Worksheets("Lists")
    Set listRange = wsList.Range("A1:A2")
    Set cell = ws.Range("W2")
    listOptions = "Delete, Classify"

    ws.Unprotect Password:=PassW

    With importTable

        ws.Unprotect Password:=PassW

        Set DecisionColumn = importTable.ListColumns.Add
        DecisionColumn.Name = "Select Decision"

        Range("W2").Select
        Set valRange = Range(cell, cell.End(xlDown))
        valRange.Locked = False
        valRange.Locked = False

        If .Parent.FilterMode Then .Range.AutoFilter
 
        .Range.AutoFilter Field:=22, Criteria1:="Error"

        With valRange.Validation
            .Delete 'delete previous validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Formula1:=listOptions
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = False

        End With

        Application.Run "Protect"

    End With

    End Sub
excel