error on add validation
 

I get an error on the 'add' line of the following code:

With rng.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator:=xlGreater, Formula1:="-9999999999999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Must be an Amount."
.ShowInput = True
.ShowError = True
End With

I set rng to a range on the worksheet. What can I look for that would be
causing this error?
Thanks

error on add validation
 

It works for me. The only issues MIGHT be...
1) I don't see you declaring rng as Range
2) -9999999999999999 seems a bit excessive.
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"ranswrt" wrote:

I get an error on the 'add' line of the following code:

With rng.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator:=xlGreater, Formula1:="-9999999999999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Must be an Amount."
.ShowInput = True
.ShowError = True
End With

I set rng to a range on the worksheet. What can I look for that would be
causing this error?
Thanks

error on add validation
 

- Is the range protected?
- Does the range contain more than one cell?
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"ranswrt" wrote:

I changed it to -9000000 and I get an error. rng is declared as a range. I
used rmng with some other with statements and it works fine. The error I get
is

Method 'add' of object 'validation' failed.

I added rng.select before the with statement and it seemed to have stopped
the error. The procedure had been working earlier then it quit working. I
don't understand why. Any ideas?

"Gary Brown" wrote:

It works for me. The only issues MIGHT be...
1) I don't see you declaring rng as Range
2) -9999999999999999 seems a bit excessive.
--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"ranswrt" wrote:

I get an error on the 'add' line of the following code:

With rng.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator:=xlGreater, Formula1:="-9999999999999999"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Must be an Amount."
.ShowInput = True
.ShowError = True
End With

I set rng to a range on the worksheet. What can I look for that would be
causing this error?
Thanks