Runtime error 1004

[Guest]

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 ?

 

[Guest]

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.

 

[Guest]

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.

 

[Bill Renaud]

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.

 

[Bill Renaud]

If a cell already has data validation, then you have to use the .Modify
method.