Syntax for adding data validation to a worksheet via vba

[Thebatfink]

Hi,

I need to add data validation to a range of cells in many workbooks and I'm trying to perform this using vb so they dont have to manually open up each one and do it one at a time. I'm struggling with the syntax to make this happen. Using the macro record I came up with this but it doesn't like it for some reason..

      ws2.Range("a7:a40").Select
        With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$R$1:$R$5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Nor does it like..

ws.Range("a7:a40").Validation.Formula1 = "=$R$1:$R$5"

Can anyone tell me the correct syntax to use. Thanks.

 

[doofusboy]

Perhaps the problem is not with your validation code, but somewhere else in your VBA code. For example, your active workbook/worksheet may not be what you think it is when you are trying to set the validation. You can either set breakpoints and run your code in debug mode to track this down or post here exactly what error(s) you're encountering in order to receive more help.

 

[Thebatfink]

Hi, Thanks for the reply. I actually got this to work with some expirementing (the result which worked is below) but it looks very ugly - like the recorded macro's of excel and I'm sure it could be simplified down somewhat.. Looking at this are there any recommendations you could suggest?

    ws.Select
    ws.Range("a6:a63").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$ap$2:$at$2"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With