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

Question

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

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. — Rory
– Rory, Commented Jun 13, 2014 at 10:13

Hoang Speed · Accepted Answer · 2019-05-24 08:04:30Z

1

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

answered May 24, 2019 at 8:04

Hoang Speed

1871 gold badge2 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jothiboss · Accepted Answer · 2019-09-16 07:40:41Z

1

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

answered Sep 16, 2019 at 7:40

Jothiboss

111 bronze badge

1 Comment

Krystian Over a year ago

that was it for me, none of the other answers mention it. a simple check to see if the worksheet is protected to unprotect it and protect it back after did the trick

Sriram · Accepted Answer · 2014-06-13 13:08:50Z

0

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 Sheet 2

Sheet1

.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

edited Jun 13, 2014 at 13:08

answered Jun 13, 2014 at 5:25

Sriram

4493 gold badges8 silver badges16 bronze badges

4 Comments

Gajju Over a year ago

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

Sriram Over a year ago

@Gajju : If you have "option explicit" statement at the top, remove it and then try to run your code. Let me know

Gajju Over a year ago

The sheet in which I have written the code, I haven't written Option Explicit at top

Sriram Over a year ago

@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

guitarthrower · Accepted Answer · 2014-06-13 15:27:31Z

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.

Sancarn · Accepted Answer · 2019-01-15 11:28:46Z

0

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.

answered Jan 15, 2019 at 11:28

Sancarn

2,93426 silver badges54 bronze badges

Comments

Daniel C · Accepted Answer · 2020-03-26 12:34:08Z

0

I ran into this issue and the problem was that it worked first time but not any subsequent times. The issue was that I was trying to add validation (.Validation.Add) to a cell that already had a list validation on it. I had to stick in a line to clear the validation (.Validation.Delete) before it.

answered Mar 26, 2020 at 12:34

Daniel C

1

Comments

Saurabh Arya · Accepted Answer · 2021-12-28 14:09:31Z

0

I ran into the same error just now and after spending close to half an hour on it,

I found that I had checked "R1C1 reference style" in the Options- Formulas- Working with formulas

As soon as I unchecked that option, everything started working fine again.

answered Dec 28, 2021 at 14:09

Saurabh Arya

431 silver badge4 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

Comments

1 Comment

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related