Tuesday, December 9, 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

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.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog