excel - 使用 VBA 在 UserInterfaceOnly 保护工作表上设置验证时，如何修复“运行时错误 '1004'”？

Question

当我尝试使用 VBA 在单元格上设置验证时，我收到“运行时错误 '1004'：应用程序定义或对象定义错误”。使用设置为 true 的 UserInterFaceOnly 标志来保护工作表。

我尝试将特定功能/子程序移动到空白工作簿，以查看错误是否重复出现，并且也没有保护以查看它是否仍然有效。问题是一样的，如果工作表没有受到保护，代码就可以工作。我用谷歌搜索了这个问题，发现下面的链接表明验证字符串有字符限制，但即使将其限制为 200 个字符也不能解决问题。 https://answers.microsoft.com/en-us/msoffice/forum/all/what-is-limit-of-the-number-of-entries-in-excel/9ce4a909-8b03-428f-94a4-1b13433aa399

'pasting this code into a blank workbook, running ps and then running test should reproduce the problem
'set specific protection with pw
Sub ps()
    ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="secret"
    ThisWorkbook.Worksheets("Sheet1").Protect Password:="secret", UserInterFaceOnly:=True
End Sub

'general protection sub
Public Sub protectSht(ws As Worksheet, pass As String)
    ws.Protect Password:=pass, UserInterFaceOnly:=True
End Sub

'set validation on cells
Sub setValidation(rng As Range, lst As Collection)
    Dim tmpstr As String
    Dim loopVar As Variant

    For Each loopVar In lst
            tmpstr = Trim(loopVar) & ", " & tmpstr
    Next loopVar

    If lst.Count = 0 Then
        rng.Validation.Delete
    Else
        With rng.Validation
        .Delete
        .Add xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=tmpstr
        End With
    End If
End Sub

'test function reproducing error
'run this on a sheet that's protected using ps
Sub test()
    Dim tst As New Collection
    tst.Add "hello"
    tst.Add "bye"
    tst.Add "etc"
    setValidation ThisWorkbook.Sheets(1).Range("B1:B5"), tst
End Sub

这应该在单元格上设置验证，但无论它们是否被锁定，它都无法这样做。相反，它给出了 "Run-time error '1004': Application-defined or object-defined error" 。

标签： excelvba