Edit data validation formula from vba

[zlackoff]

I have a cell that uses list type data validation. I need to be able to update the list of allowable values via a macro and would greatly prefer not using a range in excel. I've been able to create the string that I want as the formula but cannot change the formula. I'm not very familiar with this aspect of excel macros (data validation) so this is my best guess.

tempStr = "=" & Range(cellWithValidation).Validation.Formula1 & ", " & valueToAdd
Range(cellWithValidation).Validation.Formula1 = tempStr

I'm not sure if i need the "=" at the beginning of tempStr but it doesn't work either way. Also, I checked to make sure tempStr is the full list and it's correct, the second line is where the error happens.

The error is: Compile Error. Wrong number of arguments or invalid property assignment.

A fix to my code or a better solution equally welcome. Thanks in advance.

[rylo]

Hi

More like

tempstr = Range(cellWithValidation).Validation.Formula1 & "," & valuetoadd
  Range(cellWithValidation).Validation.Delete
  
  Range(cellWithValidation).Validation.Add xlValidateList, , , tempstr

rylo

[zlackoff]

worked beautifully, thank you!