Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell value against a list
When my user completes entry into the spreadsheet they press a button that
looks through all the cells and checks for certain error conditions (no commas, must be numeric ...). I would like to add a event that checks the value in the cell to the list of values assigned to it. Most of my fileds have drop down lists (created as lists) but not all so I would need to be find out if the list exists for the cell and then if the cell value is valid. Most of my issues around this are from the users pasting values into the cells and bypassing the list validation. Thanks in advance for any help. This group has been very helpful |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell value against a list
When the user pastes a value, they probably destroy your data validation
criteria as well (in the act). That said, you can probably use the change event see Chip Pearson's page on Events http://www.cpearson.com/excel/events.htm -- Regards, Tom Ogilvy "Hippy" wrote in message ... When my user completes entry into the spreadsheet they press a button that looks through all the cells and checks for certain error conditions (no commas, must be numeric ...). I would like to add a event that checks the value in the cell to the list of values assigned to it. Most of my fileds have drop down lists (created as lists) but not all so I would need to be find out if the list exists for the cell and then if the cell value is valid. Most of my issues around this are from the users pasting values into the cells and bypassing the list validation. Thanks in advance for any help. This group has been very helpful |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell value against a list
Hippy,
To prevent pasting, you could use an event. For example, copy the code below, right-click the sheet tab, select "View Code", and paste the code into the window that appears. It will prevent pasting into cells B5 and C6 on that sheet. You can modify the range to include all cells where you don't want the user to be able to bypass validation using paste. HTH, Bernie MS Excel MVP Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Target, Range("B5,C6")) Is Nothing Then Exit Sub If Application.CutCopyMode Then Application.CutCopyMode = False MsgBox "Sorry, you can't paste values into those cells!" End If End Sub "Hippy" wrote in message ... When my user completes entry into the spreadsheet they press a button that looks through all the cells and checks for certain error conditions (no commas, must be numeric ...). I would like to add a event that checks the value in the cell to the list of values assigned to it. Most of my fileds have drop down lists (created as lists) but not all so I would need to be find out if the list exists for the cell and then if the cell value is valid. Most of my issues around this are from the users pasting values into the cells and bypassing the list validation. Thanks in advance for any help. This group has been very helpful |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data/Validate/List | Excel Worksheet Functions | |||
how do you validate a list with more than one column | Excel Discussion (Misc queries) | |||
How to validate list data from a different workbook | Excel Discussion (Misc queries) | |||
validate data from list without picklist | Excel Discussion (Misc queries) | |||
validate list xl2000 | Excel Programming |