Disable Clear DContents command in excel - vba code??
in [Programming]
|
From: dazzag82 on 7 Jan 2010 11:59 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?
From: Roger Govier on 7 Jan 2010 12:33 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 "dazzag82" <dazzag82(a)discussions.microsoft.com> wrote in message news:D6A31C99-E017-4A24-A4BE-66C138C99310(a)microsoft.com... > 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
From: Rick Rothstein on 7 Jan 2010 13:29 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" <dazzag82(a)discussions.microsoft.com> wrote in message news:D6A31C99-E017-4A24-A4BE-66C138C99310(a)microsoft.com... > 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?
|
Pages: 1 Prev: Automated report & external data source Next: Use Macro to Format UserForm Object |