This doesn't remove the option, but it does remove the user's ability to
change a value that already exists (whether by using Clear Contents or by
hitting the Delete key or by editing the cell to an empty string)...

'******************** START OF CODE ********************
Dim CurrentValue As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C3:G12")) Is Nothing Then
If Len(CurrentValue) 0 And Target.Value = "" Then
MsgBox "Sorry, but you are not allowed to clear existing values!"
Application.EnableEvents = False
Target.Value = CurrentValue
Application.EnableEvents = True
Target.Select
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C3:G12")) Is Nothing Then
CurrentValue = Target.Value
End If
End Sub
'******************** END OF CODE ********************

To implement this code, right click the worksheet that you want to have this
functionality, select View Code from the popup menu that appears and then
copy/paste the above code into the code window that opened up. Next, change
my example range of C3:G12 to the range of cells you actually want to
protect. That is it. Go back to the worksheet and you will see that, for the
range of cells you specify, you can enter a value into an empty cell by you
cannot change a cell that has a value already in it.

--
Rick (MVP - Excel)


"dazzag82" wrote in message
...
Is there a way to disable the "Clear Contents" option when right clicking
on
a cell.

Basically I have spreadsheet which has lots of data validations and
formulas
so the worksheets are protected. Each row of data represnts information
about
a specific project. The sheet protection does not allow users to delete
rows
or columns, but conetnets can be cleared leaving blank cells. I do not
want
users to have the ability to clear contents of cells once data has been
entered. Yes they can click the cell and manually override the information
eg
if a date changes etc, but I do not want the contents to be cleared once
data
has been entered.

Is there a macro or vba code which can disable the "Clear Contents" option
in the right mouse button menu.

I dont want users clearing conetnts of cells to pretend that projects did
not exist etc or if a project is cancelled we still want to have
visibility
that it was planned at one stage. Basically once information has been
entered
into a cell it cannot be cleared. This will only apply to specific cells,
not
all cells in the worksheet.

Please help?