Help - My list validation has been breeched!

Lt. Krul

New Member
Joined
May 6, 2002
Messages
32
I've set up a validation on a cell that only allows a certain list of items (project numbers) to be entered. If any number outside that list is entered I've set up an error message to pop up. The types of numbers in the list look like: 99127, 2001-271, 2002-124-2 etc. It's a long yet very exacting list. HOWEVER someone entered the following number and the cell validated even though its not found on the list... that number was 2001-*271 (note the asterist) Not this plays havoc with another database since this number is not truely a valid number. Is there some glitch with validation where *'s are conserned? I know that thay can be used for wild cards, but I do not want numbers like 2001-*271 to validate. Any Ideas out there?

Thanks

Lt. Krul.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

white6174

Board Regular
Joined
May 6, 2002
Messages
137
Was this value put in by paste,
validation doesn't work in this case

steve w
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, I have written the following code as a test to put up a message box if someone enters the number 2 in cell A1. If I paste 2 from another cell into A1 then the change event still brought up the message box! This would suggest paste isnt the problem.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a1").Value = 2 Then
MsgBox "You have entered 2!!"
End If
End Sub

How are you performing the validation? If its from a list then the best option is perhaps a drop down box on the spreadsheet so the user can only choose one of those options.

cheers
Parry
 

Lt. Krul

New Member
Joined
May 6, 2002
Messages
32
the validation is from a list, but its a combo box of sorts, allowing the user to either type in a number or use the drop down. I just thought about something...the list shows up on another tab in the same worksheet (its an external data get list from an Acess Database) Its possible that the user "added" the bogus number to the list just to throw me...I wouldn't put it past him. I've got to check this out on Tuesday. The tabe with the list is normally hidden, but users can get to it to look at the entire list if they want. Is there a way to keep the list from being added to but at the same time allow the refresh data operation to take place each time they open the workbook? Questions...questions...

Lt. Krul.
 

Lt. Krul

New Member
Joined
May 6, 2002
Messages
32

ADVERTISEMENT

regarding if it was pasted or not...I really do not know how he put it there. I'll know more Tuesday.
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, sorry I dont know enough but links between Excel & Access. Hopefully someone else can help :)
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

How exactly are you creating the ComboBox? Are you using a Toolbox and putting them there yourself?

I think the cleanest way to do this is through Data-Validation. On Settings tab of the Validation dialog, you can select validation criteria.

Perhaps you are using Data Validation, but criteria is currently set to "Any value". I would hope your possible values are known beforehand, and are a list referenced by Data Validation.

The list can be on that sheet, in "white ink"; the values are there, it just can't be seen.

Make sure that all the cells with Data Validation (linked to lists) are FormatCells-Protection-Locked (checkbox is checked).

When you have everything set up, then Tools-Protection-Protect Sheet.

The cell will then be protected from cut-and-paste; only list values can be selected.
This message was edited by stevebausch on 2002-09-01 06:51
 

Lt. Krul

New Member
Joined
May 6, 2002
Messages
32
OK, Here's what I have...
Validation is set to "LIST" and "IN CELL DROPDOWN" (thats my "Combo" box I guess). The List is strictly defined and no other numbers are allowed, none that is except for the ones with an asterisk somewhere in them. Also the Validation box "Ignore blanks" is NOT checked. When I check "ignore blanks" it seems to accept any number. I cannot lock these cells for formatting because when I do, then I cannot have blank entries (which is often the case in these cells). Plus the users have to scroll through the entire list of numbers (some 6000) to get to the one they want (not the best option) I could place in the list a "null" value number that they can pick for their blanks. Its a nuisance but I might be the way to go...
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
I went back to my suggestion, I thought it worked, but not with Excel 2002.

As far as your situation, it sounds like a job for some VBA, in the Worksheet_Change event.

Good Luck,
Steve
 

Forum statistics

Threads
1,144,061
Messages
5,722,287
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top