Hi Ben,

I tried with "rng.select" first (one singel cell) but with no luck.

I created a errorhandler:
Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in
procedure ValidationYr of Module Restore_Grund"
and it told me:

"Error -2147417848 (Automation error
The object invoked has disconnected from its clients. ) in procedure
ValidationYr of Module Restore_Grund"
Can't say it tell me anything I can use, does it for you?


-- Further, If I run a macrorecording and insert the formula in
manual GUI, it work o.k. After that, the code also worked ok. If I
close and open the file, I might get the error back again, I guess ...

-- A friend of mine ran this on his computer and got the same error,
so it isn't a bad excel installation thing either...

Any other of you people out there have any ideas? Feel free to tell
me.

Kind regards
Tskogstrom


---------------------------------------


On 4 Juni, 16:11, Ben McBen
wrote:
In my experience validation can be very fussy/quirky when it comes to VBA,
and in my code I always make sure that the cell that is having the validation
applied to it is the active/selected cell - this seems to prevent any of
these errors. If someone knows of a explanation for this I would love to
hear it.

Obviously I would normally frown on selecting/activating objects I am
working with, but this is the only way I could get it reliable



"tskogstrom" wrote:

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- Dölj citerad text -

- Visa citerad text -