In this link [Data validation only allows listed values]
it is clear that to enter values NOT on a validation list you must use the Error Alert set to Information and check the box for showing the error message when invalid values are entered. This is NOT how OpenOffice works. By default, a validation list will accept values not on the list without requiring an input message to appear. This created a problem described below:
I have a sheet (sheet name passed as aSheet) on which I regularly update values in a column (A) based on changes in another column (B). The macro below is used to first reset the validation list for column A. However, the code below
Sub ValidList(aSheet,R1,R2,List,vType) ’ Create a Validity list
rem A formula is passed as a List & pasted to cells in a column from R1 to R2
rem If List = “” and vType = 0 then all Values should be accepted for updating column A
oCurs = aSheet.createCursorByRange(aSheet.getCellRangeByName(R1 & “:” & R2))
oCurs.gotoStart()
oValidation = oCurs.Validation
oValidation.setFormula1(List)
oValidation.IgnoreBlankCells = True
oValidation.ShowList = 2
oValidation.ErrorAlertStyle = 2
oValidation.ShowInputMessage = True
oValidation.InputTitle = “TT List” : oValidation.InputMessage = “Your value is not on the List”
oValidation.Type = vType
oCurs.Validation = oValidation
End Sub
This macro is called before updating the values in column A (R1 = “A1” ; R2 = “A20”) on Sheet aSheet. This macro should revise the validation so that the validation is set to “All Values” for the cells A1 to A20 (oValidaton.Type = vType where vType = 0) and the List is blank. However, it does not work. It remains set to its default value of a List (vType = 6) and the list is not removed.
This change in validation was not needed in OpenOffice as even vType=6 (List) accepts non-list values. I did use the same SUB with vType = 0 and List = “” to remove validation lists in OpenOffice with no problems.
Since my macro does not reset the validation type to All Values, I cannot update the column A values with the column B values in another macro.
If anyone has any suggestions, I would appreciate them.