Validate cell when contents are pasted into it

[JR_06062005]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
>

[JR_06062005]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> >
> >
>
>
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> > >
> > >
> >
> >
> >