DropBox w/out user override through paste

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

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
 
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
 
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 fire:
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
 
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
 
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
 
Back
Top