Data Validation

[Paul W Smith]

Rather than writing some VBA to restrict entries into a cell I thought I
would use the data validation!

All I am trying to do is restrict the user to having to select from the from
pick from list box. I believe I have set things up correctly, but I cannot
stop the use from adding spaces!

The use is restricted from using his own codes, but I cannot seem to stop
him from manually adding (in error) unnecessary spaces either before or
after his selection. What happens is he selects from the list, but he can
them manually edit the cell. It does not allow him to use entries that are
not in the list but it does allow them to insert spaces e.g. "EUR" can
become " EUR", now Vlookups do not match.

Am I doing some thing wrong. IN the vaidation dialog box I :

Allow: List
Ignore space: (I have tried both true and false)
Source: EUR,GBP,USD

Am I doing something wrong or does validation not work sufficent well to
allow me to only have my user have entered the codes I have set above,
without any leading or trailing spaces.

Paul Smith

 

[Shane Devenshire]

Hi,

This is non-standard behavior. What version of Excel are you using? Where
is the Source list and what does it have in it. Is the list handled via List
or Custom? Finally, choose Data, Validation, Error Alert tab, and check to
see if Style is set to Stop.

Also, the user can copy and paste a value into a cell with a Data Validation
in which case they wipe out the Data Validation and can enter anything.

 

[Dave Peterson]

I can't duplicate this in xl2003.

Is there any chance you've changed something on the Error Alert tab of that
data|validation dialog?

And until you get it fixed, you could use:

=vlookup(trim(a1), ....

But that shouldn't be necessary.

 

[Gord Dibben]

Paul

You misunderstand the purpose of "ignore blanks"

If the source list is a named range that contains blank cells, users may be
able to type any entry, without receiving an error message. To prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.

I cannot replicate your problem in Excel 2003 or 2007

I don't know why your users are allowed to enter a <space> EUR unless you
have error alert turned off, but then they should be able to enter anything.

Does your original list of items have any leading spaces?


Gord Dibben MS Excel MVP

 

[dranon]

Paul

You misunderstand the purpose of "ignore blanks"

If the source list is a named range that contains blank cells, users may be
able to type any entry, without receiving an error message. To prevent this:

Select the cell that contains a data validation list
Choose Data|Validation
On the Settings tab, remove the check mark from the Ignore blank box.
Click OK
Note: If the source list contains blank cells, and is a range address, e.g.
$A$1:$A$10, it will block invalid entries with Ignore blank on or off.

I cannot replicate your problem in Excel 2003 or 2007

I can in Excel 97.

 

[Paul W Smith]

I do not believe I have mis-understood the purpose of 'ignore blanks', I was
just trying to I had covered all bases in caswe som eone suggested I needed
to untick this!

I am using XL2003 (11.8237.8221) SP3

I have isolated my problem as being the fact I am not using an Excel range
as my valid source entries, I had typed them straight into the source box
separated by commas. I would have thought this would have worked and it
does to the degree I mentioned earlier but it allows the behaviour I have
subsequently described.

I guess this is what soem woudl say a bug, but if I use a range to set my
criteria rather than actually specifying it in teh Data|Validation Sourced
all works as it should.

Thanks dranon for being able to udnerstand and replicate my flaw.

Paul Smith

 

[Dave Peterson]

I copied your list directly from your post and pasted it into that dialog and I
still couldn't duplicate the problem.

Does it happen if you start a brand new workbook, too?

 

[Paul W Smith]

Dave,

I have re-created the issue and send it to you privately.

PWS

 

[Dave Peterson]

I don't open attachments to unsolicited mail.

Dave,

I have re-created the issue and send it to you privately.

PWS

 

[Paul W Smith]

Fair enough, but do not say you have copied my issue and it's not a problem
for you!

What are you afraid of from a workbook that does not conatined any VBA
code - how is that going to possibly contain anything nasty?

 

[Dave Peterson]

Just one of my personal rules.

Good luck.

Fair enough, but do not say you have copied my issue and it's not a problem
for you!

What are you afraid of from a workbook that does not conatined any VBA
code - how is that going to possibly contain anything nasty?

 

[Gord Dibben]

Upload the file to a file-host, post the link to that file-host here.

If someone wants to take a look they will download the file to their
computer.

http://www.freefilehosting.net/
http://savefile.com/


Gord Dibben MS Excel MVP

Fair enough, but do not say you have copied my issue and it's not a problem
for you!

What are you afraid of from a workbook that does not conatined any VBA
code - how is that going to possibly contain anything nasty?

 

[Paul W Smith]

http://freefilehosting.net/download/45kbc

Great Advice Gord.

I actually did not know that free sites like this exised!

If anyone wants to see the problem check out the link above.

I can confirm that the workbook does not contain any VBA code!

Upload the file to a file-host, post the link to that file-host here.

If someone wants to take a look they will download the file to their
computer.

http://www.freefilehosting.net/
http://savefile.com/


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Paul

Looks like a bug.

I can re-produce on a clean workbook in 2003 and 2007. Leading or trailing
spaces do not produce and invalid data error.

If you use the range =B7:B9 as source instead of comma de-limited list it
won't allow the spaces.

So, that's the workaround AFAICS


Gord

 

[Paul W Smith]

Thanks Gord for confirming I was not going mad and that the fault actually
did exist, despite what some others posted.

As I posted earlier in the thread I had found the workaround which was to
use a range for the Source. Let's hope someone at Microsoft is reading this
and notes the bug for future releases!

PWS