Hello all, If you need to disable the "CUT" feature of Excel within a
worksheet then the following works fine. The code disables the "CUT"
while you are within your file and restores the "CUT" function when you
exit your file - it stops cutting using shortcut keys or toolbar
functions. Please feel free to use the following - if you find you can
make the following VBA code better please feel free to reply to this
thread.
-- Place the following VBA in the "ThisWorkbook" area --
Private Sub Workbook_Open()
Run "DisableCut"
Run "Find_Disable_Commands"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "EnableCut"
Run "Find_Enable_Commands"
End Sub
-- Place the following VBA in a new Module --
Sub DisableCut()
On Error Resume Next
With Application
'disables shortcut keys
.OnKey "^x", ""
'Disables Cut
.CommandBars("Standard").FindControl(ID:=21).Enabl ed = False
.CommandBars("Edit").FindControl(ID:=21).Enabled = False
.CommandBars("Cell").FindControl(ID:=21).Enabled = False
.CommandBars("Column").FindControl(ID:=21).Enabled = False
.CommandBars("Row").FindControl(ID:=21).Enabled = False
.CommandBars("Button").FindControl(ID:=21).Enabled = False
.CommandBars("XLM Cell").FindControl(ID:=21).Enabled = False
.CommandBars("Formula Bar").FindControl(ID:=21).Enabled = False
.CommandBars("Query").FindControl(ID:=21).Enabled = False
.CommandBars("Query Layout").FindControl(ID:=21).Enabled = False
.CommandBars("Object/Plot").FindControl(ID:=21).Enabled = False
.CommandBars("Phonetic Information").FindControl(ID:=21).Enabled =
False
.CommandBars("Shapes").FindControl(ID:=21).Enabled = False
.CommandBars("Inactive Chart").FindControl(ID:=21).Enabled = False
.CommandBars("ActiveX Control").FindControl(ID:=21).Enabled =
False
.CommandBars("OLE Object").FindControl(ID:=21).Enabled = False
.CommandBars("Excel Control").FindControl(ID:=21).Enabled = False
.CommandBars("WordArt Contex Menu").FindControl(ID:=21).Enabled =
False
.CommandBars("Curve").FindControl(ID:=21).Enabled = False
.CommandBars("Pictures Contex Menu").FindControl(ID:=21).Enabled =
False
.CommandBars("Rotate Mode").FindControl(ID:=21).Enabled = False
.CommandBars("Connector").FindControl(ID:=21).Enab led = False
.CommandBars("Script Anchor Popup").FindControl(ID:=21).Enabled =
False
.CommandBars("Worksheet Menu Bar").FindControl(ID:=21).Enabled =
False
End With
End Sub
Sub EnableCut()
On Error Resume Next
With Application
'enables shortcut keys
.OnKey "^x"
'Enables Cut
.CommandBars("Standard").FindControl(ID:=21).Enabl ed = True
.CommandBars("Edit").FindControl(ID:=21).Enabled = True
.CommandBars("Cell").FindControl(ID:=21).Enabled = True
.CommandBars("Column").FindControl(ID:=21).Enabled = True
.CommandBars("Row").FindControl(ID:=21).Enabled = True
.CommandBars("Button").FindControl(ID:=21).Enabled = True
.CommandBars("XLM Cell").FindControl(ID:=21).Enabled = True
.CommandBars("Formula Bar").FindControl(ID:=21).Enabled = True
.CommandBars("Query").FindControl(ID:=21).Enabled = True
.CommandBars("Query Layout").FindControl(ID:=21).Enabled = True
.CommandBars("Object/Plot").FindControl(ID:=21).Enabled = True
.CommandBars("Phonetic Information").FindControl(ID:=21).Enabled =
True
.CommandBars("Shapes").FindControl(ID:=21).Enabled = True
.CommandBars("Inactive Chart").FindControl(ID:=21).Enabled = True
.CommandBars("ActiveX Control").FindControl(ID:=21).Enabled = True
.CommandBars("OLE Object").FindControl(ID:=21).Enabled = True
.CommandBars("Excel Control").FindControl(ID:=21).Enabled = True
.CommandBars("WordArt Contex Menu").FindControl(ID:=21).Enabled =
True
.CommandBars("Curve").FindControl(ID:=21).Enabled = True
.CommandBars("Pictures Contex Menu").FindControl(ID:=21).Enabled =
True
.CommandBars("Rotate Mode").FindControl(ID:=21).Enabled = True
.CommandBars("Connector").FindControl(ID:=21).Enab led = True
.CommandBars("Script Anchor Popup").FindControl(ID:=21).Enabled =
True
.CommandBars("Worksheet Menu Bar").FindControl(ID:=21).Enabled =
True
End With
End Sub
Sub Find_Disable_Commands()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, ID:=21) '21 = cut
For Each ctl In myControls
ctl.Enabled = False
Next ctl
End Sub
Sub Find_Enable_Commands()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
Set myControls = CommandBars.FindControls _
(Type:=msoControlButton, ID:=21) '21 = cut
For Each ctl In myControls
ctl.Enabled = True
Next ctl
End Sub
Save the file then re-start Excel and the "CUT" should be disabled.
--
sparx
------------------------------------------------------------------------
sparx's Profile:
http://www.excelforum.com/member.php...o&userid=16787
View this thread:
http://www.excelforum.com/showthread...hreadid=537613