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
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)
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
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.