i require a formula for custom data validation to permit entry 5 digit numeric characters only.
No spaces, or alpha permitted.
Note that the cell format will be text, to enable leading zeros to be entered & displayed (as permitted)
Thanks.
refer previous post for similar for aplha only.
http://www.excelforum.com/excel-work...ml#post2007297
I was unable to adapt for my new requirement.
Any help would be appreciated
Hi,
this is not Excel Programming, i.e. VBA, it should not be in the programming forum. But to answer your question:
use the custom validation formula
=AND(LEN(A1)=5,VALUE(A1)>0)
which checks if the text string is 5 characters and also checks if the value of the text string interpreted as a number would be greater than zero.
This causes an error when entering the formula. I expect this is due to having the cell formatted as text.
Hi,
you must format the cells to be text. Then apply the data validation to the cells. The attached workbook has the yellow highlighted cells formatted as text and the data validation applied.
hth
I still have this issue with error when updating manually or via VBA.
But not always.
See error screenshots and sample file with vba.
Any help as to cause of error would be appreciated.
thats coz a1 is empty just click ok and if a1 formated as text it will work
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
thanks for your reply.
How can i prevent vba error if cell is empty.
Other than if blank, insert a value, add validation, then clear cell value.
try the programming forum!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks