Validation Error Message stuck

[nowonda]

Hey guys,

Ran into some weird thing today. Or maybe it's just weird because I'm
not an VBA whiz..

I tried to enter a validation rule for a range in VBA that looked like
this (got it straight for VBA Help content):

With Range("e5").Validation
..Add Type:=xlValidateWholeNumber, _
AlertStyle:= xlValidAlertStop, _
Operator:=xlBetween, Formula1:="5", Formula2:="10"
..InputTitle = "Integers"
..ErrorTitle = "Integers"
..InputMessage = "Enter an integer from five to ten"
..ErrorMessage = "You must enter a number from five to ten"
End With

I only modified the range to resemble my named range and that's it
(just did it to see how it works at first). After I saw it did work, I
wanted to customize the validation function - i.e. the minimum and
maximum values and the message to be displayed and so on. Now here's
what I do not understand: no matter how I modify the validation
function, it's stuck as I saved it in the first place. So even if I
change the messages or change the numbers, Excel still uses the first
validation I entered for that range. I even commented and after that
deleted the code. Weird enough, even with the code deleted, the
validation was still in place...

Any thoughs?

Thanks for any ideas.

m

 

[Norman Jones]

Hi NoWonda,

Try changing:

With Range("e5").Validation

to

With Range("e5").Validation
.Delete

 

[Executor]

Hi,

Try:

Sub myValidation()
Dim strFrom As String
Dim strUpto As String
Dim strInput As String
Dim strError As String

strFrom = "10"
strUpto = "20"
strInput = "Enter an integer from ten to twenty"
strError = "You must enter a number from ten to twenty"

With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop,
_
Operator:=xlBetween, Formula1:=strFrom, Formula2:=strUpto
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Integers"
.ErrorTitle = "Integers"
.InputMessage = strInput
.ErrorMessage = strError
.ShowInput = True
.ShowError = True
End With
End Sub

Specialy the .Delete part is importend.
This remove the active validation.

If you add some validation using VBA the validation is added to the
sheet.
Any validation you add to a sheet is samed with the sheet.
So if you reopen the sheet the valiadation is still active.



Hoop this helps,


Executor