Validation OK to named ranges in Veryhidden sheets?

[tskogstrom]

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_MaxNumberYears;
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

 

[Guest]

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

 

[Bob Phillips]

I would guess that is because you are trying to add a list with more
elements than remain in your OFFSET range.

If maxyears = 9, then OFFSET by that number takes you to the 10th element.
11-maxyears gives 2, but there is only 1 from the 10th onwards.

I think the formula should be

=OFFSET(OFFSET_YR1_10;REF_MaxNumberYears;0;COUNTA(OFFSET_YR1_10)-REF_MaxNumberYears;1)"

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[tskogstrom]

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

 

[tskogstrom]

Thank you for trying to help, but i belive formula is ok..

OFFSET_YR1_10 is one cell above the firs row and end on "9" and 11-
REF_MaxNumberYears make "2", giving the list of "9,10". And actually,
the offset function doesn't know I want to have "9,10" in the list, it
would in your suggestion just give some empty row in the list instead.

And if I add it manually in excel GUI, it work ok...

Kind regards
Tskogstrom
--------------------------------------------------

 

[Guest]

THis is my actual procedure:


Public Sub SetValidation(varrValues, rngTarget As Range)

If rngTarget.Worksheet.Name <> ActiveSheet.Name Then
rngTarget.Worksheet.Activate
End If


rngTarget.Select

With rngTarget.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(varrValues, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

End With
Range("A1").Select
End Sub

 

[Bob Phillips]

Well you may believe that, I don't. I reproduced your error and I explained
what caused it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thank you for trying to help, but i belive formula is ok..

OFFSET_YR1_10 is one cell above the firs row and end on "9" and 11-
REF_MaxNumberYears make "2", giving the list of "9,10". And actually,
the offset function doesn't know I want to have "9,10" in the list, it
would in your suggestion just give some empty row in the list instead.

And if I add it manually in excel GUI, it work ok...

Kind regards
Tskogstrom
--------------------------------------------------

 

[Guest]

Simplest next step would be for you to substitute the formula part of the
validation with a constant expression e.g. "1,2,3,4" and see if the error
persist - then you can validate whether the issue is with the construction of
the list, or the setting of the validation. If this works OK then I think
Bob is right.

The automation error I have seen occurs with a variable of which I can test
the value.


HTH

 

[tskogstrom]

I'll try that if the problem occure again. Now it work ok, so I can't
test it.

I have made on change also, that might help. I read in another posting
about this matter that it might be caused by the offset reference link
got "broken" somehow when you unprotect one of the linked cells.
Earlier, I unprotected the sheet for this code, but now I have
UserInterfaceOnly = True in my protection code instead.

If problem occur, I will of cource test with contants instead of the
formula, but I still can't see why that should cause the problem. The
Offset or validation function doesn't care if it is one cell more or
less or what it is inside it... and the formula usually work very
well. If the error occure, it disappear if I put the same formula from
Excel GUI.

The Offset reference is one cell above number "1" in list (Bob counted
to the tenth element but that's wrong, I don't get his error. The
"OFFSET_YR1_10" named range is one single cell placed above the 1to10
numbers) and right now, it give me a list of two items; "9","10" - as
it should.

Hopefully the solution was the protection issue.


kind regards
Tskogstrom