Syntax for adding data validation to a worksheet via vba

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
401
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..

Code:
      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..

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

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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
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.
 
Upvote 0

Thebatfink

Active Member
Joined
Apr 8, 2007
Messages
401
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?

Code:
    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
 
Upvote 0

Forum statistics

Threads
1,191,690
Messages
5,988,039
Members
440,125
Latest member
vincentchu2369

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top