Hi,

I have routines to restore format in cells at worksheet_change events,
and some of them have validation formats. I use this kind of formulas
to restore them:

With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=OFFSET(OFFSET_YR1_10;REF_MaxNumberYear s;
0;11-REF_MaxNumberYears;1)"


Sometimes I get runtime error 1004, application- or object-defined
error on the
'.Add Type:= ... " line.

ON this particulat code above, the named range 'OFFSET_YR1_10' is on a
veryhidden worksheet and have a list of cells below in column from 1
to 10. 'MaxNumberYears' is a named rage to the same sheet, with value
'9' in the cell. So the offset formula should be ok.



Is there any restrictions with Validation? I know it had to point to
the same sheet, but it work with other sheets if you have nameed
ranges. Is it because it is a veryhidden sheet?

Or maybe validation demand the workbook to be unprotected? Do you know
any restrictions that could create these runtime errors?


Kind regards
Tskogstrom