Disable Clear DContents command in excel - vba code??

[dazzag82]

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?

 

[Roger Govier]

Hi

The following event code may help in preventing the user from making a
change, once a value has been entered.
In this case I have assumed the column not to be changed is column E,
(column 5). Amend to suit

Option Explicit
Public oldval As String
Public newval As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 5 Then Exit Sub
Application.EnableEvents = False
newval = Target.Value
If oldval = "" Then
Target = newval
Else

MsgBox "You are not allowed to change this value"
Target = oldval
oldval = "": newval = ""
End If
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 5 Then Exit Sub
oldval = Target.Value
End Sub

To sue
Copy all of the above code
Right click on the sheet tab of the relevant sheet>View Code
Paste the code into the white pane that appears
Alf+F11 to return to Excel

--
Regards
Roger Govier

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?

__________ Information from ESET Smart Security, version of virus
signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Rick Rothstein]

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.