excel - vba 插入验证列表 - 工作表锁定时出现错误 1004？

Question

我有以下 vba 代码，可以正常工作；但是，当我锁定/保护工作表时，即使我在单元格格式选项中取消选中相关单元格的“锁定”，它也不起作用。

为什么我会这样

error 1004 application defined or object defined error

工作表何时被锁定？

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    If Range("B10").Value <> "" Then
        Dim MyList(9) As String
        MyList(0) = "60 Days EOM"
        MyList(1) = "60 Days DOI"
        MyList(2) = "45 Days EOM"
        MyList(3) = "45 Days DOI"
        MyList(4) = "30 Days EOM"
        MyList(5) = "30 Days DOI"
        MyList(6) = "14 Days DOI"
        MyList(7) = "10 Days EOM"
        MyList(8) = "7 Days DOI"
        MyList(9) = "Immediate Payment"
        With Range("N38").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:=Join(MyList, ",")
        End With
        If Intersect(Target, Target.Worksheet.Range("N38")) Is Nothing Then
            Range("N38").Value = "60 Days EOM"
        End If
    Else
        Range("N38").Validation.Delete
        Range("N38").Value = ""
    End If
End Sub

Answer 1

答案是，即使您解锁了单元格，数据验证仍然不受限制。在应用数据验证之前，您必须取消对工作表的保护。