How to disable delete operation

[Guest]

I have a protected sheet with a range of unlocked cells. Is it possible to
programmatically disable the delete and cut operations on these cells? I
would still like to be able to use clear contents and copy and paste

 

[Héctor Miguel]

hi, Jeff !

... protected sheet with a range of unlocked cells.
... disable the delete and cut operations on these cells?
... still like to be able to use clear contents and copy and paste

[i think that...] -> 'disable the delete and cut' ...
[it is 'against'] -> 'still be able to use clear contents' -?-

[i guess that] you might want give a try...
-> change your time time entry 'sum' formulae to [something like]...
=(((Row1>0)*(Row2-Row1))+((Row3>0)*(Row4-Row3))+((Row5>0)*(Row6-Row5)))*24

hth,
hector.

 

[Guest]

Hector,
Thank you for your reply. I'm sorry that I was not very clear in
describing my request for help. When I used Edit|Cut on a selection in the
range I've described, it cuts (and pastes) everything (formatting,
references, number formats, etc.) this is not the behavior I desired. When I
used the term delete I should have said Edit|Cut. Using the delete key on the
keyboard amounts to an Edit|Clear|Contents which produces acceptable
behavior. So, what I should really have asked was "How do I disable the
"Edit|Cut / Edit|Paste" operation. I have found a solution to this problem
with the help of a posting at MrExcel Forum. This solution works for this
situation because I have no use for Cut/Paste on this sheet but it still
allows Copy/Paste.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = xlCut Then
Application.CutCopyMode = False
Application.CellDragAndDrop = False
End If
End Sub

Regarding your suggestion for a revised formula in my total cell, I am
getting ready to try your suggestion of a revised formula as soon as I work
my way through it and figure out what it does, but a thought occured to me
that I could simply count the number of cells with a value and those with no
value and if they are not equal then I have an error which I could then
indicate. This would probably allow me to put In/Out pairs in arbitrary
positions. (I think ;-)

Thanks for your help, I appreciate it very much,
Jeff Higgins

 

[Guest]

Here is a solution I've come up with:

A class module named "EventClass" contains:

Public WithEvents App As Application
Public WithEvents CutMenuCommand As Office.CommandBarButton

Private Sub CutMenuCommand_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
CancelDefault = True
Call Sheet1_OnCut
End Sub

--------------------------------------------------------------------------------

A standard module named "WorksheetFunctions" contains:

Public AppClass As New EventClass
Public CmdBars As CommandBar

Public Sub Init_Workbook()
Set AppClass.App = Application
Set CmdBars = AppClass.App.CommandBars("Worksheet Menu Bar")
Set AppClass.CutMenuCommand = CmdBars.Controls("&Edit").Controls("Cu&t")
End Sub

Public Sheet1_OnCut()
MsgBox ("Cut menu_item Clicked")
End Sub

----------------------------------------------------------------------------

"ThisWorkbook" module contains:

Private Sub Workbook_Activate()
If AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand =
CmdBars.Controls("&Edit").Controls("Cu&t")
End If
End Sub

Private Sub Workbook_Deactivate()
If Not AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand = Nothing
End If
End Sub

Private Sub Workbook_Open()
Call Init_Workbook
End Sub

--------------------------------------------------------------------------------

"Sheet1" module contains:

Private Sub Worksheet_Activate()
If AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand =
CmdBars.Controls("&Edit").Controls("Cu&t")
End If
End Sub

Private Sub Worksheet_Deactivate()
If Not AppClass.CutMenuCommand Is Nothing Then
Set AppClass.CutMenuCommand = Nothing
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Disable Edit|Cut / Edit|Paste operation in the sheet named "Sheet1"
'In case I've missed any toolbar/menu options that will cause
Cut/Copy/Paste
If AppClass.App.CutCopyMode = xlCut Or AppClass.App.CutCopyMode = xlCopy
Then
AppClass.App.CutCopyMode = False
AppClass.App.CellDragAndDrop = False
End If
End Sub