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 ? |
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 ? |
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 ? |
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 |
Runtime error 1004
If a cell already has data validation, then you have to use the .Modify
method. -- Regards, Bill Renaud |
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com