Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable delete operation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable delete operation
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]... =(((Row10)*(Row2-Row1))+((Row30)*(Row4-Row3))+((Row50)*(Row6-Row5)))*24 hth, hector. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable delete operation
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 "Héctor Miguel" wrote: [i guess that] you might want give a try... - change your time time entry 'sum' formulae to [something like]... =(((Row10)*(Row2-Row1))+((Row30)*(Row4-Row3))+((Row50)*(Row6-Row5)))*24 hth, hector. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to disable delete operation
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 "Jeff Higgins" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'07 Large Operation Error (Delete / Insert Rows and/or Columns) | Excel Discussion (Misc queries) | |||
How do I disable or delete an Add-In | Excel Discussion (Misc queries) | |||
Disable the Delete Key | Excel Discussion (Misc queries) | |||
Disable Delete | Excel Discussion (Misc queries) |