Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell when contents are pasted into it
I have a set of cells which are validated against a list so that when someone
types content into the cell, what is typed must be consistent with the validation list. This does not require VB code and works fine when values are typed into the cells, but not when they are pasted. I use the worksheet's Worksheet_SelectionChange(ByVal Target As Range) subprocedure and an If Then statement to detect when a particular set of cells changed, and set validation ranges according to the contents of reference cells. But I don't have any idea how to validate values pasted into a cell or cell range against a list. I would appreciate any help I can get. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell when contents are pasted into it
bValid = False
for each cell in Range("ValidationRange") if Ucase(Target.Value) = ucase(Cell.Value) then bValid = True exit for end if Next if not bValue then Application.Undo End if End Sub -- Regards, Tom Ogilvy "JR_06062005" wrote in message ... I have a set of cells which are validated against a list so that when someone types content into the cell, what is typed must be consistent with the validation list. This does not require VB code and works fine when values are typed into the cells, but not when they are pasted. I use the worksheet's Worksheet_SelectionChange(ByVal Target As Range) subprocedure and an If Then statement to detect when a particular set of cells changed, and set validation ranges according to the contents of reference cells. But I don't have any idea how to validate values pasted into a cell or cell range against a list. I would appreciate any help I can get. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell when contents are pasted into it
I've got 2 questions regarding your answer:
1) Which Sub procedure do you recommend I put the code into? 2) Do I declare bValid as a boolean variable? Thanks "Tom Ogilvy" wrote: bValid = False for each cell in Range("ValidationRange") if Ucase(Target.Value) = ucase(Cell.Value) then bValid = True exit for end if Next if not bValue then Application.Undo End if End Sub -- Regards, Tom Ogilvy "JR_06062005" wrote in message ... I have a set of cells which are validated against a list so that when someone types content into the cell, what is typed must be consistent with the validation list. This does not require VB code and works fine when values are typed into the cells, but not when they are pasted. I use the worksheet's Worksheet_SelectionChange(ByVal Target As Range) subprocedure and an If Then statement to detect when a particular set of cells changed, and set validation ranges according to the contents of reference cells. But I don't have any idea how to validate values pasted into a cell or cell range against a list. I would appreciate any help I can get. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validate cell when contents are pasted into it
I assume the change event fires when you paste. So I would recommend the
change event. (haven't tested it an don't recall) bValid would be dimmed as boolean. -- Regards, Tom Ogilvy "JR_06062005" wrote in message ... I've got 2 questions regarding your answer: 1) Which Sub procedure do you recommend I put the code into? 2) Do I declare bValid as a boolean variable? Thanks "Tom Ogilvy" wrote: bValid = False for each cell in Range("ValidationRange") if Ucase(Target.Value) = ucase(Cell.Value) then bValid = True exit for end if Next if not bValue then Application.Undo End if End Sub -- Regards, Tom Ogilvy "JR_06062005" wrote in message ... I have a set of cells which are validated against a list so that when someone types content into the cell, what is typed must be consistent with the validation list. This does not require VB code and works fine when values are typed into the cells, but not when they are pasted. I use the worksheet's Worksheet_SelectionChange(ByVal Target As Range) subprocedure and an If Then statement to detect when a particular set of cells changed, and set validation ranges according to the contents of reference cells. But I don't have any idea how to validate values pasted into a cell or cell range against a list. I would appreciate any help I can get. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validate Cell contents in formlua | Excel Worksheet Functions | |||
Worksheet_Selection Change code will not allow cell contents to be copied and pasted onto the sheet it's running on | Excel Programming | |||
Newbie question: clear contents in first column of copy-pasted row(s) | Excel Programming | |||
How to validate in code the contents of a cell? | Excel Programming | |||
Validate value in cell | Excel Programming |