Excel VBA数据验证VBA运行时错误1004“应用程序定义的错误或对象定义的错误" [英] Excel VBA Data validation VBA Runtime Error 1004 “Application-defined or Object-defined error”

问题描述

我在这里想念的是什么?该代码应像在单一执行中一样工作，也可以在循环中工作.

What I am missing here? The code should work as in single execution and also in a loop.

Public mySheet As Worksheet
Set mySheet= Sheets("CARS")

此行出现错误->

   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(optionList, ",")

错误是->

VBA运行时错误1004应用程序定义或对象定义的错误

VBA Runtime Error 1004 "Application-defined or Object-defined error

Public Function addDataValidation(row As Long)

Dim optionList(2) As String

optionList(0) = "1"
optionList(1) = "2"
optionList(2) = "3"

    With mySheet.Cells(row, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(optionList, ",")
    End With

   With mySheet.Cells(row, 3).FormatConditions.Add(xlCellValue, xlEqual, "=1")
        .Font.Bold = True
        .Interior.ColorIndex = 4
        .StopIfTrue = False
    End With

    With mySheet.Cells(row, 3).FormatConditions.Add(xlCellValue, xlEqual, "=2")
        .Font.Bold = True
        .Interior.ColorIndex = 6
        .StopIfTrue = False
    End With

    With mySheet.Cells(row, 3).FormatConditions.Add(xlCellValue, xlEqual, "=3")
        .Font.Bold = True
        .Interior.ColorIndex = 3
        .StopIfTrue = False
    End With

    With mySheet.Cells(row, 3)
            .HorizontalAlignment = xlCenter
            .Value = optionList(0)
    End With

End Function

不合逻辑的行为的原因是该函数在另一个类模块中与同名函数混为一谈.

解决方案是1

Private Function addDataValidation(row As Long)

解决方案2

Check always cells protection status.

推荐答案

对我来说，如果将Excel窗口最小化，此行可能会由于指定的错误而失败( ThisWorkbook.Windows(1).WindowState = xlMinimized )，就像尝试冻结窗格一样.

For me, this line can fail with the specified error if the Excel window is minimized (ThisWorkbook.Windows(1).WindowState = xlMinimized), just as when attempting to freeze panes.

我最终创建了一个辅助子例程:

I ended up creating a helper subroutine:

Private Sub EnsureNotMinimized() ' #ILoveVBA
    If ThisWorkbook.Windows(1).WindowState = xlMinimized Then
        MsgBox "...", vbExclamation + vbOKOnly, msgBoxTitle
        ThisWorkbook.Windows(1).WindowState = xlMaximized
    End If
End Sub

并在整个代码库中散布其调用...

and sprinkling its invocations across the codebase...

这篇关于Excel VBA数据验证VBA运行时错误1004“应用程序定义的错误或对象定义的错误"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！