Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 with VBA and validation
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 with VBA and validation
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... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 1004 with VBA and validation
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error 1004 when using VBA to set Validation from list on another s | Excel Programming | |||
Runtime-error 1004 (excel 2007) when selecting validation list optiontriggering worksheet_change property | Excel Programming | |||
1004 Cell Protected Error for data validation lists when locked and protected | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Error 1004 with Data Validation in VBA | Excel Programming |