CODEWITHSUNDEEP
excelvbavalidation
SanomaJean
SanomaJean

Reputation: 181

VBA Adding Data Validation Programmatically

I've looked online for an hour and can't find any solution that has worked for me. I have a range of cells which I want to apply data validation to each cell in that range based on another range. I am not sure if I need a For Each loop or if I can just assign the validation to the whole list at one time. Here is what I have so far: 

With Range(rngMonthStart, rngMonthEnd)
    With .Validation
         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:= ("='Settings'!" & totalrange.Address)
    End With
End With

When it gets to the .Add line I get an Application Defined Error. Any help is super appreciated

Upvotes: 0

Views: 1507

Answers (2)

bkyee
bkyee

Reputation: 74

In case you have done the .Delete, and it still fail at .Add with "Application Defined Error". Please check if the worksheet is protected or not. It should run fine once the worksheet is unprotected.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29362

You seem to be overwriting the validation. If it already exists, overwriting leads to runtime error. Try:

With .Validation
    .Delete  
    .Add ...

Upvotes: 4

Related Questions