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.

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.

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.

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.