Update validation on multiple worksheets at once

[sim]

I have cells on multiple worksheets all with the same validation settings.
When i change the validation settings of one such cell, i can select to change all similar cells... but this works only within the active worksheet. If I sleect multiple worksheets, the "Validation..." tool is greyed out.

Is there a way to change the validation settings of similar cells on multiple worksheets simultaneously?

[sim]

I posted this item yesterday, and I'm wondering if I was clear.
I'm guessing there's a simple enough Macro that would enable me to change the Validation setting of all the cells in an entire workbook (ie across multiple sheets simultaneously) which currently use the same validation settings. (I know that I can select the box to change the validation for all similar cells in the worksheet, but I can't figure out how to do it for all simlar cells in *other* worksheets at the same time.) I have 10 files, each with 24 worksheets, and all the sheets need the same validation settings, so a Macro would save a bundle of time! I'd greatly appreciate if someone could point the way! Thanks a lot.

[mdbct]

The following will change the validation on all sheets for all cells that are similar to the selected cell, assuming that the current selection's address is the same across the sheets. You can add more of the setting, that are assigned to the validation. I've only demonstrated the Type and Formulaq, but the process would be the same for all of the settings.

Please Login or Register  to view this content.

The settings recorded by the macro

Please Login or Register  to view this content.

[sim]

Thanks a lot!
I'm not clear how to use this. I made a macro as shown below: not sure that i incorporated the "settings" correctly. But how do i use it? if i first make a change to a cell, then there won't be any cells like it at that point. but if i first invoke the Macro, then how does it know what change to apply across multiple worksheets? also, can i control which worksheets it applies to? (i'd need it to apply to all but one sheet in a 25-sheet workbook.)

Apologies for my inexperience: thanks again for your help!

Sub allValids()
Dim iShts As Integer, rngVal As Range, i As Integer
Dim vType, vFormula1

iShts = Sheets.Count
Set shtCur = ActiveSheet
Set rngVal = Selection
vType = rngVal.Validation.Type
vFormula1 = rngVal.Validation.Formula1

For i = 1 To iShts
With Sheets(i).Range(rngVal.Address).SpecialCells(xlCellTypeSameValidation).Validation
.Delete
.Add Type:=vType, Formula1:=vFormula1
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1,2,4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With
Next
End Sub

[mdbct]

Please put you code between code tags (before you get a warning from a mod).

The process would be.
Make the changes to the Validation
Run the macro

Constraint.
All sheets must have the same validation in the same area. If you change validation on A3 (and the "same settings" cells) on sheet 1, cell A3 on the other sheets must have the validation you are wanting to change.

It is possible to skip/ignore sheets.

Can you post a sample workbook?

[sim]

(thanks again, and thanks for the warning about "code tags": can you point out to me how to do this?)

i tried uploading a sample file as an .xls, but it failed repeatedly, so i zipped it and uploaded it that way, i hope it works for you. Note: the actual file has (24) sheets (2 per month) plus one "tally" sheet. all the 24 sheets are identically laid out, but the tally sheet would need to be skipped by the macro.

as it stands, the macro isn't working: i get "No cells found".

thoughts?

[mdbct]

To use the code tags, highlight (in the full editor) the code then click on the # above the text box.

It looks like the same values are being used for the validation list, which is generated from the Tallies sheet. You can use a named range as the data validation, then assign that name to the validation lists. I've attached a basic sample. The yellow cells have the validation.

I'll look at this more if this solution does not fit your needs

[mdbct]

In order for the code to work, your currently selected cell must contain the validation your want to change. Cells A14 or C14 would work in your example book. This version will ignore the "tallies" sheet.

Please Login or Register  to view this content.

[sim]

perfect, well done. That's exactly what was needed. Thanks a lot!