VBA Adding Data Validation Programmatically

Question

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

delete the previous data validation and totalrange might have to be only one column or row stackoverflow.com/questions/22956604/… — Slai
– Slai, Commented Feb 7, 2017 at 23:25

A.S.H · Accepted Answer · 2017-02-07 23:27:01Z

4

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

With .Validation
    .Delete  
    .Add ...

answered Feb 7, 2017 at 23:27

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

1 Comment

SanomaJean Over a year ago

Ahhhh perfect. I didn't delete. Thank you.

bkyee · Accepted Answer · 2022-06-02 05:48:14Z

0

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.

answered Jun 2, 2022 at 5:48

bkyee

725 bronze badges

Collectives™ on Stack Overflow

VBA Adding Data Validation Programmatically

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related