How do you prevent users from pasting over validated cells?
 

I have a range of validated cells where the user can only pick values from
the specified list. I don't want them to be able to overwrite these cells
with values not in the list by pasting data etc. How do I prevent this?

While in your spreadsheet, unlock all those cells where you want to allow
users to enter data. (Format, Cells, Protection, untick "Locked").
Now click on Tools, Protection, Protect Sheet. There are a number of
options you can select or deselect. At this stage you can also specify a
password to unprotect the sheet, so that only you can do that. Users can
then copy data from your protected cells, but cannot overwrite it. Check
Excel Help on this

"LCK" wrote:

I have a range of validated cells where the user can only pick values from
the specified list. I don't want them to be able to overwrite these cells
with values not in the list by pasting data etc. How do I prevent this?

Thanks, but I can't lock cells in the worksheet because the users need to
populate the data for me. I am just trying to excercise a degree of control
by only allowing them to enter certain values for particular fields (to avoid
free-form text). To do this I have inserted field validation where they have
to pick the value from a list. If I locked these cells then they would not
be able to pick a value. They cannot enter any values into the cell, besides
those defined in my list, but there is a flaw in the validation in that they
can copy a value from another cell and paste it over the validated cell -
thereby eliminating the validation. How can I prevent the user from passting
over these cells, without having to lock the cells?
"Kassie" wrote:

While in your spreadsheet, unlock all those cells where you want to allow
users to enter data. (Format, Cells, Protection, untick "Locked").
Now click on Tools, Protection, Protect Sheet. There are a number of
options you can select or deselect. At this stage you can also specify a
password to unprotect the sheet, so that only you can do that. Users can
then copy data from your protected cells, but cannot overwrite it. Check
Excel Help on this

"LCK" wrote:

I have a range of validated cells where the user can only pick values from
the specified list. I don't want them to be able to overwrite these cells
with values not in the list by pasting data etc. How do I prevent this?

LCK
This is a bit involved so bear with me. The bottom line here is that
you need to disable the Paste and Paste Special menu commands whenever the
user selects one of the Data Validation cells. This is easy enough to do
but it comes with some baggage that you have to take care of. This
"baggage" is that disabling a menu command is a global setting in Excel. By
this I mean that the new setting (disable so-and-so) applies to Excel, not
just to the file that you are working with.
In short, if the user selects one of your Data Validation cells, thereby
disabling the Paste commands, and then chooses to activate or open another
Excel file, that new file will not have the Paste commands available. So
you must have code (macros) to do the following:
Disable the two Paste commands if a Data Validation cell is selected.
Enable the two Paste commands if any other cell is selected.
Enable the two Paste commands if any other file is activated or opened.
Enable the two Paste commands if your file is closed
Select a non Data Validation cell before the file is closed or another file
is activated.

In the following macros, I chose A1 as a non Data Validation cell. I also
chose "TheRng" as the range name of the range of all of your Data Validation
cells, so you have to select that range and name it TheRng.

I also chose "TheSheetName" as the name of your sheet.

I also chose "TheFileName.xls" as the name of your file.

You have to change these names in these macros to make them work with your
file and sheet.

Put the following macro in the sheet module of your sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("TheRng")) Is Nothing Then
Call Enable_Disable_Commands(22, False) 'CANNOT Paste
Call Enable_Disable_Commands(755, False) 'CANNOT Paste Special
Else
Call Enable_Disable_Commands(22, True) 'CAN Paste
Call Enable_Disable_Commands(755, True) 'CAN Paste Special
End If
End Sub

Put the following two macros in the Workbook module of your file:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call Enable_Disable_Commands(22, True) 'CAN Paste
Call Enable_Disable_Commands(755, True) 'CAN Paste Special
Sheets("TheSheetName").Activate
Range("A1").Select
ThisWorkbook.Save
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_Deactivate()
Call SelectA1
End Sub

Put the following two macros in a regular module of your file:
Sub Enable_Disable_Commands(id As Integer, Enab As Boolean)
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, id:=id)
For Each ctl In myControls
ctl.Enabled = Enab
Next ctl
End Sub

Sub SelectA1()
Dim NewFile As String
Application.ScreenUpdating = False
NewFile = ActiveWorkbook.Name
Windows("TheFileName.xls").Activate
Sheets("TheSheetName").Activate
Range("A1").Select
Application.EnableEvents = False
Windows(NewFile).Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

If you send me direct a valid email address for you, I'll send you a small
file with all these macros properly placed. My email address is
. Remove "nop" from this address. HTH Otto
"LCK" wrote in message
...
I have a range of validated cells where the user can only pick values from
the specified list. I don't want them to be able to overwrite these cells
with values not in the list by pasting data etc. How do I prevent this?