L
L. Valdés
Hello.
I will try to explain why I need to do this strange thing
and what's my problem.
For validation purposes, I need to add a second formula
to some cells, with this condition:
the second formula needs to track the position changes
of the cells she belongs to (and of the cells she points to)
The only way I have -in my humble knowledge- is using
the property formula1 from the validation object.
I use a custom validation engine and this let me choose
validation errors in a dedicated toolbar.
Unfortunately, this property (validation.formula1) needs to
be set in english, and returns a value in local language. (*)
For example:
With r.MergeArea.Validation
.Delete
.Add Type:=xlValidateCustom, Formula1:="=not(isblank(A1))"
End With
returns in the inmediate console:
?activesheet.cells(1,1).validation.formula1
=NO(ESBLANCO(A1))
When the validation engine needs to know if a cell is
validated, he needs to evaluate a local formula which
is not possible
?application.Evaluate("=NO(ESBLANCO(A1))")
Error 2029
?application.Evaluate("=NOT(ISBLANK(A1))")
Falso
Currently, I have to translate the local formula to english
with a method which is not as clean as it should be. (**)
This as been a shoddy work during some months but I'm not very
happy whit this portion of code.
So I would like to know if anyone can help me please:
1) I'm looking for a simple way to evaluate a local formula ¿any ideas?
2) If the first question is not so simple, I would
like to read the "validation.formula1" property not
as a local formula, but as an english one.
3) I would like also to set this "formula1" in local language.
¿Is it possible?
Thanks in advance.
Best regards, Luis.
(*)
This is true, as far as I know, in Excel 2000, 2002, and 2003
(**)
_____________________________________________________
Function Translate(str As String, Rng As Range) As String
'function to translate from local language to English
'Evaluate does not work with local formula
'where Rng is previously searched as the first empty cell
'who can be used as an aux cell
Rng.FormulaLocal = str
Translate = Rng.Formula
Rng.Formula = vbNullString
End Function
____________________________________________________
I will try to explain why I need to do this strange thing
and what's my problem.
For validation purposes, I need to add a second formula
to some cells, with this condition:
the second formula needs to track the position changes
of the cells she belongs to (and of the cells she points to)
The only way I have -in my humble knowledge- is using
the property formula1 from the validation object.
I use a custom validation engine and this let me choose
validation errors in a dedicated toolbar.
Unfortunately, this property (validation.formula1) needs to
be set in english, and returns a value in local language. (*)
For example:
With r.MergeArea.Validation
.Delete
.Add Type:=xlValidateCustom, Formula1:="=not(isblank(A1))"
End With
returns in the inmediate console:
?activesheet.cells(1,1).validation.formula1
=NO(ESBLANCO(A1))
When the validation engine needs to know if a cell is
validated, he needs to evaluate a local formula which
is not possible
?application.Evaluate("=NO(ESBLANCO(A1))")
Error 2029
?application.Evaluate("=NOT(ISBLANK(A1))")
Falso
Currently, I have to translate the local formula to english
with a method which is not as clean as it should be. (**)
This as been a shoddy work during some months but I'm not very
happy whit this portion of code.
So I would like to know if anyone can help me please:
1) I'm looking for a simple way to evaluate a local formula ¿any ideas?
2) If the first question is not so simple, I would
like to read the "validation.formula1" property not
as a local formula, but as an english one.
3) I would like also to set this "formula1" in local language.
¿Is it possible?
Thanks in advance.
Best regards, Luis.
(*)
This is true, as far as I know, in Excel 2000, 2002, and 2003
(**)
_____________________________________________________
Function Translate(str As String, Rng As Range) As String
'function to translate from local language to English
'Evaluate does not work with local formula
'where Rng is previously searched as the first empty cell
'who can be used as an aux cell
Rng.FormulaLocal = str
Translate = Rng.Formula
Rng.Formula = vbNullString
End Function
____________________________________________________