Excel Data Validation - List and rounding 2

[drlex]

Greetings!
I've got a wee problem with using xlValidateList and just wanted to check whether I was doing it right or whether it was an Excel quirk. Using both 2003 and 2007 'flavours' of Excel.

I want to restrict users to the entry of either 1,2 or 3 on a cell which is formatted not to show decimal places. Despite giving a drop-down list, it has been discovered that some are entering 0.5, which is accepted and shown as 1. Testing the sheet, it would appear that 0.49 is not acceptable, nor is 3.5, but numbers between 0.5 and 3.4999 are.

I can use the xlValidateWholeNumber, but then I lose the drop down list. Curiously enough, should I fill 3 cells with 1, 2 and 3 respectively and then reference them as the Formula1, then non-integer entry is blocked.

  Range("A1").NumberFormat = "0"
   With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="1,2,3"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = "Number must either 1, 2 or 3"
    .ShowInput = True
    .ShowError = True
   End With

Any thoughts and views on this "funny"?
Thanks,
lex

soi la, soi carré

 

[combo]

The easiest would be general format in A1 instead, you don't need decimals anyway.
If you can use code in the target workbook, then Worksheet_Change event with testing Target for address and value could be helpful. BTW, validation accepts any pasted value too.

combo

 

[drlex]

Thanks, combo - General would be better. Disappointing to learn of the paste issue.

I have found that changing the Formula1 to "1.,2.,3." will prevent non-integer entry and still keep a drop-down (albeit showing the decimal point), so I'll offer that as an alternative to the xlValidateWholeNumber change.

soi la, soi carré

 

[xlbo]

Looks like the reason that the validation allows 0.5 to 3.4999 is that the validation uses the cell display value rather than the entered value

As you have no dps on the number, the display value of the cell for 0.5 is 1 which is why the validation allows 0.5 (but not 0.4 as the display value for that would be 0)

As combo says, if you use General as the format, this goes away...just thought I would chime in with an explanation as to the difference between the value entered into the cell and the value actually displayed by the cell (this is the Range.TEXT property when accessed via code)

You can see this by using:

Sub test()
Debug.Print Range("A1").Value
Debug.Print Range("A1").Value2
Debug.Print Range("A1").Text
Debug.Print Range("A1").Formula
End Sub

where A1 is your test cell


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[drlex]

Of course! <*slaps forehead*>
I should have thought that Microsoft wants to validate the displayed figure and not the value itself.
Thanks, Geoff.
(Shame one can't award 'bricks' to those responsible)
I'll leave the worksheet_change amend for when the problem resurfaces - always worth keeping a little in reserve.


soi la, soi carré