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 http://stackoverflow.com/questions/22956604/how-to-add-data-validation-to-a-cell-using-vba — Slai, Feb 07 '17 at 23:25

score 4 · Accepted Answer · answered Feb 07 '17 at 23:27

4

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

With .Validation
    .Delete  
    .Add ...

answered Feb 07 '17 at 23:27

A.S.H

29,101
5
23
50

1

Ahhhh perfect. I didn't delete. Thank you. – SanomaJean Feb 07 '17 at 23:28

score 0 · Answer 2 · answered Jun 02 '22 at 05:48

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 02 '22 at 05:48

bkyee

74
5

VBA Adding Data Validation Programmatically

2 Answers2