I am trying to create a dropbox where the user can only select from the
list. I know I can do this through data validation, but the problem is that
it can be overridden if a user paste a value on to the cell. How can I have
a dropbox where the user cannot override the validation through pasting or
through any other means?

Thanks
Leo

Leo,

I believe the only solution is to write VBA code, to check the data that's
sneaked by the data validation.

Are you comfortable doing that?

Naomi


"Leo" wrote in message
...
I am trying to create a dropbox where the user can only select from the
list. I know I can do this through data validation, but the problem is
that
it can be overridden if a user paste a value on to the cell. How can I
have
a dropbox where the user cannot override the validation through pasting or
through any other means?

Thanks
Leo

Hi Naomi,
I know a little bit about writing code, but I really don't know where to
start on this one? Any ideas?

Thanks
Leo

"Naomi Hildebrand" wrote in message
news:yHW0g.595$wd2.307@trndny02...
Leo,

I believe the only solution is to write VBA code, to check the data that's
sneaked by the data validation.

Are you comfortable doing that?

Naomi


"Leo" wrote in message
...
I am trying to create a dropbox where the user can only select from the
list. I know I can do this through data validation, but the problem is
that
it can be overridden if a user paste a value on to the cell. How can I
have
a dropbox where the user cannot override the validation through pasting
or
through any other means?

Thanks
Leo

Leo,

Well, first figure out what your requirements are. Here are some questions
for you:

Do you have many cells with data validation, or just one or two?

Do you want the error message to fi
a) as soon as the user tabs out of the cell
b) when he tries to save his spreadsheet
c) when he explicitly runs a Validate macro

Naomi


"Leo" wrote in message
...
Hi Naomi,
I know a little bit about writing code, but I really don't know where to
start on this one? Any ideas?

Thanks
Leo

"Naomi Hildebrand" wrote in message
news:yHW0g.595$wd2.307@trndny02...
Leo,

I believe the only solution is to write VBA code, to check the data
that's
sneaked by the data validation.

Are you comfortable doing that?

Naomi


"Leo" wrote in message
...
I am trying to create a dropbox where the user can only select from the
list. I know I can do this through data validation, but the problem is
that
it can be overridden if a user paste a value on to the cell. How can I
have
a dropbox where the user cannot override the validation through pasting
or
through any other means?

Thanks
Leo

Hi Naomi,
I have about four or five cells with data validation. I would like the
error message to fire as soon as the user tabs out of the cell.

Thanks
Leo



"Naomi Hildebrand" wrote in message
news:kyX0g.603$wd2.28@trndny02...
Leo,

Well, first figure out what your requirements are. Here are some questions
for you:

Do you have many cells with data validation, or just one or two?

Do you want the error message to fi
a) as soon as the user tabs out of the cell
b) when he tries to save his spreadsheet
c) when he explicitly runs a Validate macro

Naomi


"Leo" wrote in message
...
Hi Naomi,
I know a little bit about writing code, but I really don't know where to
start on this one? Any ideas?

Thanks
Leo

"Naomi Hildebrand" wrote in message
news:yHW0g.595$wd2.307@trndny02...
Leo,

I believe the only solution is to write VBA code, to check the data
that's
sneaked by the data validation.

Are you comfortable doing that?

Naomi


"Leo" wrote in message
...
I am trying to create a dropbox where the user can only select from
the
list. I know I can do this through data validation, but the problem
is
that
it can be overridden if a user paste a value on to the cell. How can
I
have
a dropbox where the user cannot override the validation through
pasting
or
through any other means?

Thanks
Leo

Leo,

Does this code work for you? You'll need to repeat the logic for each cell
with validation, substituting in the appropriate column & row combinations,
and the valid values.

I'm sure there's a more elegant way, but this works...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 Then
Select Case Target.Value
Case "aaa", "bbb", "ccc"
' this is valid
Case Else
MsgBox "Value entered into cell A1 was not valid."
End Select
End If
End Sub

Naomi


"Leo" wrote in message
...
Hi Naomi,
I have about four or five cells with data validation. I would like the
error message to fire as soon as the user tabs out of the cell.

Thanks
Leo



"Naomi Hildebrand" wrote in message
news:kyX0g.603$wd2.28@trndny02...
Leo,

Well, first figure out what your requirements are. Here are some
questions
for you:

Do you have many cells with data validation, or just one or two?

Do you want the error message to fi
a) as soon as the user tabs out of the cell
b) when he tries to save his spreadsheet
c) when he explicitly runs a Validate macro

Naomi


"Leo" wrote in message
...
Hi Naomi,
I know a little bit about writing code, but I really don't know where
to
start on this one? Any ideas?

Thanks
Leo

"Naomi Hildebrand" wrote in message
news:yHW0g.595$wd2.307@trndny02...
Leo,

I believe the only solution is to write VBA code, to check the data
that's
sneaked by the data validation.

Are you comfortable doing that?

Naomi


"Leo" wrote in message
...
I am trying to create a dropbox where the user can only select from
the
list. I know I can do this through data validation, but the problem
is
that
it can be overridden if a user paste a value on to the cell. How
can
I
have
a dropbox where the user cannot override the validation through
pasting
or
through any other means?

Thanks
Leo

Naomi,
That worked. Thanks for helping a newbie

Leo


"Naomi Hildebrand" wrote in message
news:LkZ0g.1735$UK5.773@trndny01...
Leo,

Does this code work for you? You'll need to repeat the logic for each cell
with validation, substituting in the appropriate column & row
combinations,
and the valid values.

I'm sure there's a more elegant way, but this works...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 Then
Select Case Target.Value
Case "aaa", "bbb", "ccc"
' this is valid
Case Else
MsgBox "Value entered into cell A1 was not valid."
End Select
End If
End Sub

Naomi


"Leo" wrote in message
...
Hi Naomi,
I have about four or five cells with data validation. I would like the
error message to fire as soon as the user tabs out of the cell.

Thanks
Leo



"Naomi Hildebrand" wrote in message
news:kyX0g.603$wd2.28@trndny02...
Leo,

Well, first figure out what your requirements are. Here are some
questions
for you:

Do you have many cells with data validation, or just one or two?

Do you want the error message to fi
a) as soon as the user tabs out of the cell
b) when he tries to save his spreadsheet
c) when he explicitly runs a Validate macro

Naomi


"Leo" wrote in message
...
Hi Naomi,
I know a little bit about writing code, but I really don't know where
to
start on this one? Any ideas?

Thanks
Leo

"Naomi Hildebrand" wrote in message
news:yHW0g.595$wd2.307@trndny02...
Leo,

I believe the only solution is to write VBA code, to check the data
that's
sneaked by the data validation.

Are you comfortable doing that?

Naomi


"Leo" wrote in message
...
I am trying to create a dropbox where the user can only select from
the
list. I know I can do this through data validation, but the
problem
is
that
it can be overridden if a user paste a value on to the cell. How
can
I
have
a dropbox where the user cannot override the validation through
pasting
or
through any other means?

Thanks
Leo