none
Excel VBA Validation Formula yeild Application-defined error. RRS feed

  • Question

  • Hello,

    I'm trying to use VBA to add following validation to cell:

    =LEN(A1)-FIND(".";A1;1)<=3

    However i constantly recive Application-defined error.

    With Selection.Validation
            .Delete
            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=Len(A1)-FIND(""."";A1;1)"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
    End With

    Can anyone direct me towards reason why my code is failing?

    Thursday, November 7, 2019 5:29 PM

Answers

  • 1) VBA expects formulas in US format, i.e. with a comma instead of a semi-colon.

    2) The code will fail if there are cells in the selection that don't contain a dot (point), for FIND returns an error in that situation.

    Try this:

            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=IFERROR(LEN(A1)-FIND(""."",A1)<=3,TRUE)"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by helpplox Thursday, November 7, 2019 10:35 PM
    Thursday, November 7, 2019 9:55 PM

All replies

  • 1) VBA expects formulas in US format, i.e. with a comma instead of a semi-colon.

    2) The code will fail if there are cells in the selection that don't contain a dot (point), for FIND returns an error in that situation.

    Try this:

            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:="=IFERROR(LEN(A1)-FIND(""."",A1)<=3,TRUE)"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by helpplox Thursday, November 7, 2019 10:35 PM
    Thursday, November 7, 2019 9:55 PM
  • Thank you alot for your help! My code works now.
    Thursday, November 7, 2019 10:36 PM