Excel Data Validation on multiple tabs

Question

I am aware of doing cell formatting on multiple Tabs in a Excel workbook by pressing Ctrl and clicking on required Tabs (i.e by grouping multiple tabs).Do we have any similar way of adding data validation on multiple sheets or tabs?Please help so that we can save a lot of time...

Answer 1

Copy a cell containing the validation. Ctrl-Click the sheet tabs. Right-click the cell you want the validations applied to then do Paste Values > Validation.

If you're using a range of cells for your validation list, use a name for them. Highlight the range, go to Formulas then click Define Name. Enter a name for your list (i.e. "mylist") then click OK.

Select the cell to which you want to apply the validation. Open the Data Validation window. Set it to List and then enter the ff. into the formula box:

=mylist

Answer 2

If you create a named List you can use it as the reference for multiple sheets. Copy the range with data validation as you wish. Select the multiple sheets as you are familiar with. Edit>Paste Special>Validation will do what you want.

Answer 3

I picked this up from another site - with a little fiddling it did the trick!

   With ws.Range("a3:a1000").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Capabilities"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With