CODEWITHSUNDEEP
vbaexcel
Gajju
Gajju

Reputation: 443

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

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

Upvotes: 0

Views: 7235

Answers (7)

Saurabh Arya
Saurabh Arya

Reputation: 43

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.

Upvotes: 0

Daniel C
Daniel C

Reputation: 1

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.

Upvotes: 0

Jothiboss
Jothiboss

Reputation: 11

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

Upvotes: 1

Hoang Speed
Hoang Speed

Reputation: 187

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

Upvotes: 1

Sancarn
Sancarn

Reputation: 2824

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.

Upvotes: 0

guitarthrower
guitarthrower

Reputation: 5834

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.

Upvotes: 0

Sriram
Sriram

Reputation: 449

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 dropdownSheet 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

Upvotes: 0

Related Questions