Excel VBA Data validation VBA Runtime Error 1004 “Application-defined or Object-defined error”

Question

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")

Error appears in this line-->

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

Error is-->

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

The reason for illogical behavior was that the function messed up with equally named function in a different class module.

Solution is 1

Private Function addDataValidation(row As Long)

Solution 2

Check always cells protection status.

Still doesn't tell us where you are qualifying `mySheet`. Is it in a module? and is your UDF in the same module? — Zac, Sep 14 '17 at 11:12
The reason was that I had equally named method in a different class module. I changed my function from public to private and that solved the case. Many thanks. You made my day. — Antti, Sep 14 '17 at 11:19
No sure if I added anything to resolve your issue but glad it helped anyway :) — Zac, Sep 14 '17 at 11:44
@AratioD You can - should - mark your question as - self - answered — Siyon DP, Sep 14 '17 at 13:36
Both Private and protection status is not solving the error? @Antti — MD Ismail Hosen, Sep 15 '20 at 17:45

score 0 · Answer 1 · answered Jul 30 '18 at 19:56

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...

Ian M · Answer 2 · 2022-01-11T14:34:46.990

I've just had the same 1004 error with VBA when creating an input message as part of data validation for a cell. In my case I was trying to add a long message and the input message is limited to 255 characters. Anything longer creates a 1004 error.

The solution was to check the length of my message and truncate if more than 255 characters

    if len(my_message) > 255 then
       my_message = left(my_message,250) & "..."
    end if

This fixed my 1004 error

Excel VBA Data validation VBA Runtime Error 1004 “Application-defined or Object-defined error”

2 Answers2

Linked