LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 24
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
'07 Large Operation Error (Delete / Insert Rows and/or Columns) Elgee Excel Discussion (Misc queries) 4 April 23rd 23 09:02 PM
How do I disable or delete an Add-In dhstein Excel Discussion (Misc queries) 2 April 13th 09 02:10 AM
Disable the Delete Key CarolK1968 Excel Discussion (Misc queries) 3 January 30th 08 07:20 PM
Disable Delete Kevin Excel Discussion (Misc queries) 0 July 29th 07 02:58 PM


All times are GMT +1. The time now is 03:41 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"