I'm trying to dynamically add validation to my Excel spreadsheet using VBA so
I can use logic to figure out which cells get what validation. I'm using the
following code to try to validate the user entering an R, E, or P:

With Worksheets("Input").Cells(copyRow, copyCol).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=R, E, P"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

When I get to the .add line, I get an error 1004 (all the other lines work
fine if I switch the order). I've tried both Formula1:="=R, E, P" and
Formula1:="R, E, P" with the same result.

Any guidance on how to fix this? Thanks...

You are missing the Delete. It will work the first time without it but any
changes must include it. Best to always include it.

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="R,E,P"

--
Regards,

OssieMac


"stocktsi" wrote:

I'm trying to dynamically add validation to my Excel spreadsheet using VBA so
I can use logic to figure out which cells get what validation. I'm using the
following code to try to validate the user entering an R, E, or P:

With Worksheets("Input").Cells(copyRow, copyCol).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:=xlBetween, Formula1:="=R, E, P"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

When I get to the .add line, I get an error 1004 (all the other lines work
fine if I switch the order). I've tried both Formula1:="=R, E, P" and
Formula1:="R, E, P" with the same result.

Any guidance on how to fix this? Thanks...

Thanks - that worked. Sometimes it's the easiest things - I couldn't tell
you how long I was hitting my head against the wall on this...