Data validation

[Horacio]

Hi
Is there any way to insert data validation in the same column (c) of 200
spreadsheets at the same time?
The validation is the same for all the sheets.

Thanks
Horacio

 

[Jarek Kujawa]

one way (to define a validation list) might be to use this macro (that
would insert the validation into EACH worksheet in the workbook):

For Each ws In ActiveWorkbook.Worksheets
With [C1].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=range"
End With
Next ws
End Sub

to make it work name yr range containing validation list (e.g. in
worksheet 1) as "range"

HIH

 

[Horacio]

Hi Jarek,
What I´m locking for is provide a duplicates in each column C of all the
worksheets.
I´m not very well in vba. Is this code ok to do that?
I know that is possible with a data validation with a formula like
"contar-se(C:C)>1" but just one sheet at each time.
My question is: can I do this for all the sheets at same time????
Thanks +
Horacio
Jarek Kujawa escreveu:

one way (to define a validation list) might be to use this macro (that
would insert the validation into EACH worksheet in the workbook):

For Each ws In ActiveWorkbook.Worksheets
With [C1].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=range"
End With
Next ws
End Sub

to make it work name yr range containing validation list (e.g. in
worksheet 1) as "range"

HIH

Hi
Is there any way to insert data validation in the same column (c) of 200
spreadsheets at the same time?
The validation is the same for all the sheets.

Thanks
Horacio

 

[Jarek Kujawa]

select C column in any sheet
right click on any of the sheet tabs
click Select All
in the formula bar insert yr formula
press CTRL+ENTER

HIH

Hi Jarek,
What I´m locking for is provide a duplicates in each column C of allthe
worksheets.
I´m not very well in vba. Is this code ok to do that?
I know that is possible with a data validation with a formula like
"contar-se(C:C)>1"  but just one sheet at each time.
My question is: can I do this for all the sheets at same time????
Thanks +
Horacio
Jarek Kujawa escreveu:

one way (to define a validation list) might be to use this macro (that
would insert the validation into EACH worksheet in the workbook):

For Each ws In ActiveWorkbook.Worksheets
    With [C1].Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
        xlBetween, Formula1:="=range"
    End With
Next ws
End Sub

to make it work name yr range containing validation list (e.g. in
worksheet 1) as "range"

- Pokaż cytowany tekst -