![]() |
replace cut command
Here's how I replaced the cut command in sheet1:
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 |
All times are GMT +1. The time now is 09:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com