VBA error in data validation: Application defined or object defined error

excel vba validation.add error 1004
vba data validation
excel vba dynamic data validation list
excel vba data validation list named range

I have this following code

Set works1 = ThisWorkbook.Worksheets("Sheet1")
Set works2 = ThisWorkbook.Worksheets("Sheet2")
Set rangeval1 = works1.Cells(11 , 4)
Set rangeval2 = works2.Range("j322:j325")

With rangeval1.Validation
   .Delete 
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
    Formula1:="='" & works2.name & "'!" & rangeval2.Address          "Line with the error"

 End With

I am unable to figure out what the error is, becoz the code seems correct to me

I guess you are missing a comma after operator. It should be Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="='" & works2.name & "'!" & rangeval2.Address. Please let me know

It fetches me the value from sheet2 as dropdown

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=("='" & works2.Name & "'!" & rangeval2.Address)

UPDATED CODE

    Sub start()
Set works1 = ThisWorkbook.Worksheets("Sheet1")
Set works2 = ThisWorkbook.Worksheets("Sheet2")
Set rangeval1 = works1.Cells(11, 4)
Set rangeval2 = works2.Range("d2:d5")

With rangeval1.Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=("='" & works2.Name & "'!" & rangeval2.Address)

 End With
End Sub

vba Excel data Validation error 'Application-defined or object , I found a way around the issues i had, related to Excel Data Validation setup from Access via VBA. Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

I don't know why Sriram's answer isn't working for you.

The following works for me.

Option Explicit

Sub test()
    Dim works1 As Worksheet
    Dim works2 As Worksheet
    Dim rangeval1 As Range
    Dim rangeval2 As Range

    Set works1 = ThisWorkbook.Worksheets("Sheet1")
    Set works2 = ThisWorkbook.Worksheets("Sheet2")
    Set rangeval1 = works1.Cells(11, 4)
    Set rangeval2 = works2.Range("j322:j325")

    With rangeval1.Validation
        .Delete
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, _
            Formula1:="='" & works2.Name & "'!" & rangeval2.Address
    End With
End Sub

Notice the Option Explicit at the top, then the declaring of each variable with a type. You may have this in your code already. But hard to tell with what you posted in your question.

If that still doesn't work, check to see if your sheets are really named Sheet1 and Sheet2.

Dropdown validation failing with "Application-defined or object , This problem has been posted here, and at this point, I have been to try and get an better understanding of VBA approach to cell validation, but I am getting an '​Application-defined or object defined error' with this code. Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Note:

A bug I've just found, You cannot change the validation with VBA's Add method if the current Excel Selection is a Chart.

This might have been the issue that you were facing.

"Application-defined or object-defined error" message when , Microsoft Industry · Data platform · Microsoft Advertising · Licensing Dim Next_Cell As Range: Set Next_Cell = Target.Offset(columnoffset:=1) Next_Cell.​Validation. "Run-time error '1004': Application-defined or object-defined error" To work around this, I had to set the validation formula at runtime. In the Data Validation window, specify that the cells allow only Date values, and then specify the beginning and ending dates of the allowable range. You can also specify a message to be displayed (as a pop-up) when the user selects one of the cells (for example, a message reminding the user that the cell only accepts dates), and a message to be displayed if the user makes an invalid entry.

I had the same problem before. The problem is you need to active the sheet that you want to create validation and select it. The most important thing is you need to select at the range which you want to create validation.

currentSheet.Activate
currentSheet.Range("A3").EntireRow.Insert

currentSheet.Cells("C3").Select

With currentSheet.Cells("C3").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, 
    Formula1:="A,B,C,D"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .errorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Adding Data Validation Via VBA Returns 1004 Error, Add line, with a run-time error 1004, "Application-defined or object-defined error." The only suggestion I have been able to find, searching  Application-defined or operation-defined error) by defining the counters as Single share | improve this answer | follow | | | | answered Jan 6 '17 at 12:47

Please ensure your sheet is NOT protected, if so Unprotect the sheet and try again, it will work!

Vba MACRO runtime error 1004 : application defined ., It's really easy to resolve your issue As far as i remember, you can't add validation object if it already exists. You have to remove existing  What i'm trying to tell you is that, that the Select method is not required, even with PasteSpecial method.Select method starts several events, among others: OnScreenUpdating, which causes the time of code execution is going slow down.

[SOLVED] [SOLVED] Dependent List in Data Validation: Runtime Error, a run-time error of "'1004' - Application-defined or object-defined error". I thought it may have something to do with my range so I've been trying How To Connect Two Routers On One Home Network Using A Lan Cable Stock Router Netgear/TP-Link - Duration: 33:19. Richard Lloyd Recommended for you

Excel VBA programmatic validation problem, Basically I'm trying to add drop down list style validation to a cell. I believe this "​Run-Time error '1004': Application-defined or object-defined error". I've played Can you do it manually using Data>>Validation? If that works  Re: VBA: Data Validation errors. Sorry again. Heres my new code, i worked around my earlier problem. I am not attempting data validation for the text boxes and combo boxes.

Application defined or object defined error (1004) with Excel , Hi folks, I'm trying to get excel to add some data validation using a macro and I keep getting the runtime error 1004 application defined or object 

Comments
  • Which version of Excel? Excel 2007 and earlier do not allow direct references to other worksheets in data validation - you have to name the range.
  • I forgot to put the comma in the question but its there in my code in the VBA editor but I am still getting the same error
  • @Gajju : If you have "option explicit" statement at the top, remove it and then try to run your code. Let me know
  • The sheet in which I have written the code, I haven't written Option Explicit at top
  • @Gajju : Thats wierd. It should. I am using ur code as per my latest update (Check answer) and i have placed it in sheet2. Check and let me know. Chnage the range value alone. Also makes sure refrences are made to Excel library