"Application-defined or object-defined error" message when using Validation.InCellDropDown property

Question

I have two dropdown lists in my sheet, one in column 8 and one in column 9.  The left-hand list has three possible values:  PRE, POST, and OTHER.  If the selected value is OTHER, then the user is to be allowed free text entry in column 9.  If it is one of the other two values, the user is to be provided with a list of values in column 9 to select from.

I tried to carry this out as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

  Const PrePost_Column As Integer = 8

  If Target.Column = PrePost_Column Then

    Dim Next_Cell As Range: Set Next_Cell = Target.Offset(columnoffset:=1)

    Next_Cell.Validation.InCellDropdown = (UCase(Target.Value) <> "OTHER")

  End If

End Sub

If I create a simple file as described above, everything works.  But with the "real" file (a group file not created by me), results are rather different.  It still works when selecting PRE or POST.  But when selecting OTHER, I get:

  "Run-time error '1004': Application-defined or object-defined error" (on attempting to set the InCellDropDown property)

Trying out some expressions in the immediate window, I get the following:

When there is a drop-down list in column 9:

   Next_Cell.Validation.InCellDropdown = true    --> OK

   Next_Cell.Validation.InCellDropdown = false  --> Error

When there is no drop-down list in column 9 (with ActiveCell in column 8):

  ?ActiveCell.Validation.InCellDropdown   --> OK

  ?Next_Cell.Validation.InCellDropdown    --> Error

  ?Next_Cell.Validation.Value                     --> OK

  ?Next_Cell.Validation.Type                       --> Error

Can anyone think of a condition that would cause this behavior?

Answer 1

I have found the answer myself, after some effort: 

I didn't quite supply sufficient information in my original posting.  The missing information was that the dropdown lists associated with the PRE and POST values weren't the same.  To set custom lists for each of these values, I was using the INDIRECT function.  And it apparently was the presence of this function in the validation's list formula that was causing the problem, even though I was disabling the list.

To work around this, I had to set the validation formula at runtime.  And as it turned out, the corresponding property -- formula1 -- is read-only; it can only be set by the validation.add method.  So my event handler became:

  Const PrePost_Column As Integer = 8

  If Target.Column = PrePost_Column Then

    Dim Next_Cell As Range: Set Next_Cell = Target.Offset(columnoffset:=1)

    With Next_Cell.Validation

      If (UCase(Target.Value) = "OTHER") Then

        .Delete

      Else

        On Error GoTo Error_Handler

        .Add Type:=xlValidateList, Formula1:="=INDIRECT (" & ActiveCell.Address & ")"

      End If

    End With

  End If

The Error_Handler would "Resume Next" if the .Add generated error 1004 (on the assumption that the validation object already existed).

Answer 2

Hi,

Glad to know that you were able to resolve the issue and thank you for posting the suggestion as well. I am sure it would help others with the same issue.

Thank you.