vba Excel data Validation error 'Application-defined or object-defined error'
I found a way around the issues i had, related to Excel Data Validation setup from Access via VBA. 
Here:
https://msdn.microsoft.com/en-us/library/office/ff840715.aspx

For some weird reason, VBA would not set data validation based on property names.
E.g. if I use Type:=xlValidateList - nothing changes in the spreadsheet.
While the below does the job:

... .Validation.Add Type:=3, Operator:=2, AlertStyle:=2, Formula1:="YES,OK"