If you want to keep the user from pasting to B10 on Sheet1 (for example), you
could use:

Private Sub Workbook_Activate()
Application.CutCopyMode = None
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Sh.Name < "Sheet1" Then Exit Sub
If Intersect(Target, Range("B10")) Is Nothing Then Exit Sub
Application.CutCopyMode = None
End Sub

This code would be pasted into the ThisWorkbook module of the workbook. This
could be expanded for multiple cells.

Hutch

" wrote:

On Mar 20, 10:26 pm, Tom Hutchins
wrote:
If you want the user to enter a value into a cell - whether by direct input,
choosing a value from a dropdown list, or as the result of running a macro
-that cell will have to be unlocked (although the macro can unprotect the
sheet, put the data into the cell, and re-protect the sheet in a split
second). With data validation, the cell is unlocked but the user is limited
to what can be entered, and if the sheet is protected, the user can't remove
the validation. With a combobox or listbox control, only the linked cell has
to be unprotected. It can be in a hidden row or column (which the user can't
unhide) or on another (hidden) sheet. You could even add data validation to
the linked cell to make sure the user doesn't enter an incorrect value into
it.

Hutch


Thanks. The problem with data validation is that its error protection
only extends to direct keyboard input. A user can hit DEL to blank it
out or copy another unprotected cell into the validated cell and
thereby get around the validation. I guess I'll have to use combobox
and hide the unprotected cell as the best solution.