Step by step guidance needed to disable cut,copy, paste, drag and drop for novice

[avnthk]

Hello Experts,

I have a column in excel where the data is being fed with use of RFID instrument. It's a serial input device. I don't want this data (Specific column of data) to be copied,cut,paste or drag.

I tried many VBA codes online. Nothing is working for me. I use office 2013. Probably I'm doing something wrong.

Can you guide me step by step? I'm a novice. You may want me to give detailed procedure.

I will really appreciate your help.

Best Regards,
Avnish

 

[Jaafar Tribak]

Hi and welcome to the forum.

Can you show us the code(s) you have tried so far ?

 

[JoshWolski]

Try

Highlight the whole worksheet, right-click, Format Cells, Protection Tab, uncheck Locked
Then highlight the whole column you need protected right-click, Format Cells, Protection Tab, check Locked
Then at the top ribbon go to Review, under the protect section click Protect Sheet, from there you have a bunch of options
To keep information secure give the worksheet a password, uncheck Select Locked Cells, the confirm password again
There are other features there you may be interested in but the will basically get you what you are looking for

Keep in my these "password protected" workbooks/worksheets, if one was interested, could find ways to break the password. Just know your audience.

 

[avnthk]

Hi and welcome to the forum.

Can you show us the code(s) you have tried so far ?

See appended code in Bold.

Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
Call EnableMenuItem(21, Allow)
Call EnableMenuItem(19, Allow)
Call EnableMenuItem(22, Allow)
Call EnableMenuItem(755, Allow)
Application.CellDragAndDrop = Allow
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub
Sub CutCopyPasteDisabled()
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

Another code:

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub


I tried appended as well. didin't understand everything in instruction. Just pasted the code in window opened from Alt+F11 and then pressed Alt+Q.

VBA Express : Excel - Disable Cut, Copy, Paste

Disable Cut, Copy, Paste It may be desirable to prevent users from cutting, copying, or pasting data into or from your workbook. This code disables all of those functions.

www.vbaexpress.com

There were others too. Seems like I don't know how apply it well.


Pls guide me

 

[avnthk]

Try

Highlight the whole worksheet, right-click, Format Cells, Protection Tab, uncheck Locked
Then highlight the whole column you need protected right-click, Format Cells, Protection Tab, check Locked
Then at the top ribbon go to Review, under the protect section click Protect Sheet, from there you have a bunch of options
To keep information secure give the worksheet a password, uncheck Select Locked Cells, the confirm password again
There are other features there you may be interested in but the will basically get you what you are looking for

Keep in my these "password protected" workbooks/worksheets, if one was interested, could find ways to break the password. Just know your audience.

Hi Josh wolski,

Thanks for your response.

I would be entering the data in the column I want it to be disabled for copy/paste/cut/drag...

Can you advise other way?

 

[JoshWolski]

SubRoutine 1:

Private Sub Workbook_Open()
Dim MyName As String
MyName = Application.UserName
If MyName = "putyournamehere" Then
Workbooks(WorkbookName).Worksheets(Sheetname).Unprotect (Password)
End If
End Sub


SubRoutine2:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks(WorkbookName).Worksheets(Sheetname).Protect (Password)
End Sub


This will unprotect the worksheet if your application.username equals MyName
Then on close of the workbook will protect the sheet again, if anyone else tries to open it it will not open, keep in mind you will have to lock you vba code as well.

you just need put these two lines in the Workkbook project object

 

[avnthk]

SubRoutine 1:

Private Sub Workbook_Open()
Dim MyName As String
MyName = Application.UserName
If MyName = "putyournamehere" Then
Workbooks(WorkbookName).Worksheets(Sheetname).Unprotect (Password)
End If
End Sub


SubRoutine2:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Workbooks(WorkbookName).Worksheets(Sheetname).Protect (Password)
End Sub


This will unprotect the worksheet if your application.username equals MyName
Then on close of the workbook will protect the sheet again, if anyone else tries to open it it will not open, keep in mind you will have to lock you vba code as well.

you just need put these two lines in the Workkbook project object

Hi JoshWolski,

Thanks for you response and help.

I'm really sorry, but I don't understand:

This will unprotect the worksheet if your application.username equals MyName
Then on close of the workbook will protect the sheet again, if anyone else tries to open it it will not open, keep in mind you will have to lock you vba code as well.

you just need put these two lines in the Workkbook project object.

I don't understand coding much. What should be appplication.username and Everything else.
It all went above my head. Can you put it for someone who has no experience in VBA coding?

As show in above image. I want to column A to be editable but not open for copy/cut/paste/drag....

Can you help?