Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
I get an error "Application-defined or object-defined error" when validating
input in cell N4 of my worksheet. For other cells in the worksheet (columns A-K) it works fine. I've checked all settings, there's no difference with other cells. What's wrong ? I use the following code: With Worksheets(1).Range("N4").Validation .Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="1", _ Formula2:="8" .ErrorMessage = "Enter digit between 1 and 8" .ShowInput = False .ShowError = True End With If I put in on error resume next, I don't get an error, it works fine. But: excel shows : value must be between 1 and 8 iso. my error message ...... ! Is this an Excel 2003 bug ? How to resolve this ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
I would think that it has to be related to the cell "N4". Formulas, links,
etc. Your code is textbook perfect, so it should work, and probably is working since you get the error message. The trick is to find what causes N4 to do that. "Tom Emmery" wrote: I get an error "Application-defined or object-defined error" when validating input in cell N4 of my worksheet. For other cells in the worksheet (columns A-K) it works fine. I've checked all settings, there's no difference with other cells. What's wrong ? I use the following code: With Worksheets(1).Range("N4").Validation .Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="1", _ Formula2:="8" .ErrorMessage = "Enter digit between 1 and 8" .ShowInput = False .ShowError = True End With If I put in on error resume next, I don't get an error, it works fine. But: excel shows : value must be between 1 and 8 iso. my error message ..... ! Is this an Excel 2003 bug ? How to resolve this ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
Your code is fine, what happens is that once you have setup the validation on
N4, if your code goes back to N4 and tries to place the same exact validation, it errors out. What you may want to try is to delete the range N4 if you want to use it again. Range("N4").delete and if you re-run the adding of the validation you will see that the error does not occur. -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Tom Emmery" wrote: I get an error "Application-defined or object-defined error" when validating input in cell N4 of my worksheet. For other cells in the worksheet (columns A-K) it works fine. I've checked all settings, there's no difference with other cells. What's wrong ? I use the following code: With Worksheets(1).Range("N4").Validation .Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="1", _ Formula2:="8" .ErrorMessage = "Enter digit between 1 and 8" .ShowInput = False .ShowError = True End With If I put in on error resume next, I don't get an error, it works fine. But: excel shows : value must be between 1 and 8 iso. my error message ..... ! Is this an Excel 2003 bug ? How to resolve this ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
You might have to add a .Delete method to your code, to insure that
there is not already data validation in that cell, for some reason. With Worksheets(1).Range("N4").Validation .Delete .Add Type:=xlValidateWholeNumber, _ AlertStyle:=xlValidAlertStop, _ Operator:=xlBetween, Formula1:="1", _ Formula2:="8" .ErrorMessage = "Enter digit between 1 and 8" .ShowInput = False .ShowError = True End With When I ran your macro more than once, then I got the same error ("Run-time error '1004': Application-defined or object-defined error"). Data validation is not totally foolproof. In Excel 2000, you can cut and "paste special value" a value outside your range, and the data validation error message will not be displayed. I also think it is generally a good idea to include an Input message as well as an Error message, so that the user knows that the cell has data validation in it. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runtime error 1004
If a cell already has data validation, then you have to use the .Modify
method. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' application or object defined error | Excel Programming | |||
runtime error '1004' application or object defined error. Please help | Excel Programming | |||
Uh oh...Runtime error 1004!!!! | Excel Programming | |||
Runtime error 1004 | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |