Help - My list validation has been breeched!

[Lt. Krul]

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.

 

[white6174]

Was this value put in by paste,
validation doesn't work in this case

steve w

 

[parry]

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.

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]

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]

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

 

[parry]

Hi, sorry I dont know enough but links between Excel & Access. Hopefully someone else can help

 

[stevebausch]

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]

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]

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