adding validation cause error 1004 in vba

Question

I define some names in name manger like validation_list1 that refers to =OFFSET(Sheet3!$C$4,,,COUNTIF(Sheet3!$C$4:$C$399,"?*"))

I want to add validation list using vba . code below cause error 1004:

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=validation_list1"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
    ' Code with error
End With

but when I delete equal sign (=) from the text assigned to Formula1 every thing will be ok

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="validation_list1"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = False
    'code without error!
End With

both above codes are similar but first one has Formula1:="=validation_list1" and second one has Formula1:="validation_list1" . why first one cause error 1004? I have to add Formula1:="=validation_list1" , how?

I do not have any problem with defining name manager , the problem is adding equal sign between quotation marks (the text assigned to Formula1 ). It does not matter what is the text , I tested it by Formula1:="=test" and caused error too then deleted = sign and wrote Formula1:="test" and everything were good.

Edit: Thanks chris neilsen . this piece of code is the output of Record Macro and if I manually select a cell and from Data tab and select Data Validation and enter the text =validation_list1 in Source field of list validation , everything will be ok but using vba causes error

SOLVED Thank you very much @chrisneilsen , your advices helped me to find the reason. Individually every things seem to be ok .formula , scope and named range were correct . This validation occurs after the program does some calculations and changes to sheets and formulas , so one cell which indirectly related to validation change to #REF . I used Define Name for formula of that cell and BINGO

@farid Help me to help you. Did you check the things I mentioned? As I said, it works for me. There must be something else about your workbook you haven't told us — chris neilsen
– chris neilsen, Commented Dec 16, 2020 at 22:32
@chrisneilsen Yes I checked all of them. I do not know what else about my workbook might exist — farid
– farid, Commented Dec 16, 2020 at 22:44
@farid what result did you get when you typed =validation_list1 in a cell? — chris neilsen
– chris neilsen, Commented Dec 16, 2020 at 22:46

chris neilsen · Accepted Answer · 2020-12-16 23:00:19Z

As you know, you must include the = in the Formula1 assignment. If you don't then you are assigning a literal string, not a named range.

When you assign the Formula1 value, this error will occur if:

The named range doesn't exist (as happens when you tried Formula1:="=test"), or
The named range is Worksheet scoped, and you try to use it in Validation on a different sheet
The named range exists but its "RefersTo" is not valid

I tried you setup, named range and code. It worked for me, using Excel 365

Sub test()
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=validation_list1"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With
End Sub

Name definition

Validation List data

After running code with B4:B5 selected

Proof that Named range works

Things to check

Check validation_list1 is Workbook scoped
The data in Sheet3!C4:C399 is valid (no errors, no gaps)
there are no typos in the named range name or formula
type =validation_list1 in a cell. What do you see?

Collectives™ on Stack Overflow

adding validation cause error 1004 in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related