Excel: Call a procedure from Data > Validation? 1

[mikevh]

Hi --

One of the columns in my worksheet calls for fancier
validation than I could figure out how to do with any
of the options in Data > Validation. I wrote a procedure
to do it and I'm calling it from the sheet's
SelectionChange event. This works okay, but I'm
wondering if I could somehow call it from
Data > Validation instead, say from "Custom", perhaps?
I couldn't figure out any way of specifying the procedure there that didn't either result in an error message or
just do nothing.

Thanks.

 

[Zathras]

You can't put a custom formula directly in the custom validate, but you can reference a cell that has a custom formula.

For example, if Cell A1 has the custom validation rule

   =C1

and cell C2 has the formula:

   =MyValidate(A1,B2)

And MyValidate is a user-defined function such as

Option Explicit
Function MyValidate(x, TestCell As Range) As Boolean

  ' If TestCell is yellow, then x must be <= 13
  ' otherwise x must be <= 6

  If TestCell.Interior.ColorIndex = 6 Then
    If x > 13 Then
      MyValidate = False
    Else
      MyValidate = True
    End If
  Else
    If x > 6 Then
      MyValidate = False
    Else
      MyValidate = True
    End If
  End If
End Function

Try to enter numbers > 6
Change the background color of cell B2 to yellow and try again up to >13.

As you can see, you can make the function as complex as you need. If still more parameters are needed, just add them to the list.

 

[mikevh]

Thanks alot, Zathras!