Tuesday, 9 December 2014

How to disable Cut,Copy,Paste and Delete Keys In Excel VBA

Excel VBA Application.Onkey to Disable Cut,Copy,Paste and Delete Keys
By using the Application.Onkey we can disable a particular Key or key combination or run a macro when you use a particular key or key combination. 

The Key argument can specify any single key combined with ALT, CTRL, or SHIFT, or any combination of these keys. Each key is represented by one or more characters, such as the  "^ {c}" for Key Combination "Ctrl+C", or "{TAB}" for the TAB key etc.

You can assigns "YourMacroName" to the key sequence CTRL+SHIFT+ENTER , as follows
Application.OnKey "^+{ENTER}", "YourMacroName"

Symbols used in Key Combinations:

Shift key = "+" (plus sign)
Ctrl key = "^" (caret)

Alt key = "%" (percent sign)

Example-I :
The following Macro is used to disable the Cut,Copy,Paste and Delete Key in Excel.
You can call this Macro in Worksheet or Workbook event to Disable or Enable Keys as per your requirement.

Sub Disable_Enable_CutCopyDel_Keys()

To Disable Copy,Cut,Past and Delete Keys
Application.EnableEvents = False

Application.OnKey "^{c}", ""              -----------------'Copy
Application.OnKey "^{x}", ""              -----------------'Cut
Application.OnKey "^{v}", ""              -----------------'Paste
Application.OnKey "{DEL}", ""

To Disable Cell Drag And Drop In Excel
Application.CellDragAndDrop = False

To Enable back the Copy,Cut,Past and Delete Keys

Application.EnableEvents = True

Application.OnKey "^{c}"              -----------------'Copy
Application.OnKey "^{x}"              -----------------'Cut
Application.OnKey "^{v}"              -----------------'Paste
Application.OnKey "{DEL}" 

To Enable back Cell Drag And Drop In Excel
Application.CellDragAndDrop = True

End Sub

Example-II :
The following Macro is used to disable the Cut,Copy options in mouse Right Click options.
You can call this Macro in Worksheet or Workbook event to Disable or Enable options as per your requirement.

Sub DisEnable_CutCopy_on_Mouse_Right_Click()

Dim Dis_Ctrl As Office.CommandBarControl

'Disable Cut Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=21)
         Dis_Ctrl.Enabled = False
     Next Dis_Ctrl

'Disable Copy Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=19)
          Dis_Ctrl.Enabled = False
     Next Dis_Ctrl

'Enable Cut Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=21)
            Dis_Ctrl.Enabled = True
     Next Dis_Ctrl

'Enable Copy Option from Mouse Right Click
     For Each Dis_Ctrl In Application.CommandBars.FindControls(ID:=19)
            Dis_Ctrl.Enabled = True
     Next Dis_Ctrl

End Sub

Thanks,
TAMATAM


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts