validation macro doesn't work

**.paul.** · 01-26-2009, 12:04 PM

i've written a macro that selects a range of cells (that part works) then sets data validation for those cells. it doesn't cause an error but it doesn't stop alphabetic characters being typed in the cells as its supposed to.

heres my code:

With Selection.Validation
       .Delete
       .Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
       Operator:=xlBetween, Formula1:=1, Formula2:=65536
       .IgnoreBlank = True
       .InCellDropdown = True
       .InputTitle = ""
       .ErrorTitle = "Integers"
       .InputMessage = ""
       .ErrorMessage = "You must enter a number from 1 to 65536"
       .ShowInput = False
       .ShowError = True
End With

what am i doing wrong?

**tinyjack** · 01-26-2009, 12:14 PM

Works for me.

Are you sure that Selection is pointing at where you think it is?

How does it not work?

TJ

**.paul.** · 01-26-2009, 12:21 PM

the selection is correct i think.

it runs ok, but it doesn't stop invalid entries. its supposed to accept only integers but it accepts anything...

**tinyjack** · 01-26-2009, 12:28 PM

How are you entering the data into the cell?

I just recorded a macro in 2007 and the code is the same as yours.

Is there much more to your code, if not could we see the rest?

TJ

**.paul.** · 01-26-2009, 12:33 PM

i'm trying to set validation in the macro. the inputting is manually in the worksheet.

**tinyjack** · 01-26-2009, 12:38 PM

Not much else I can suggest, the code should work. Double check that you are not doing anything to the cell in the macro (copying another cell contents to it, etc) and that Selection is where you think it is.

Just a long shot as well, Validation will only report an error when you hit the return key.

TJ

**.paul.** · 01-26-2009, 12:55 PM

my mistake. your last guess was right. i could've sworn it was stopping me typing invalid characters before, but as you said it validates when you move away from the cell.

thanks for your help...