Mar 26 2021 11:48 AM
I have seen various articles desribing ways to block or disable cut, copy and paste in Protected Excel Workbook for unlocked cells and I can say I have tried almost everything. The most appealing I found out of the others was the code mentioned in this link: https://www.extendoffice.com/documents/excel/4289-excel-disable-cut-copy-paste.html
So this code works as it appeals as it disables cut, copy and paste functionalities from Excel to Excel.
Luckily, I happened to test the same from Notepad or Non-Excel aplication to Excel. It is still letting copy data from Non-Excel Application like Notepad or Chrome to Excel.
I am not an expert in VBA, so if there is anything which can be added to block other pasting options it would great.
Code from the link for reference:
Mar 26 2021 02:50 PM
You could also try this: add a worksheet change event handler (or a workbook_sheetchange event handler) to check if the last action in the undo list is "Paste" and, if so, undo.
Private Sub Worksheet_Change(ByVal Target As Range)
Const procName As String = "Worksheet_Change"
Dim lastAction As String
'// Get the last action performed by user.
On Error Resume Next
lastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
On Error GoTo ErrHandler
'// Check if the last action was paste.
If Left(lastAction, 5) = "Paste" Then
With Application
.EnableEvents = False
.Undo
End With
End If
ExitProc:
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox prompt:="Error " & Err.Number & ": " & Err.Description, Title:=Me.Name & "." & procName
Resume ExitProc
End Sub
Mar 27 2021 05:01 AM
Mar 21 2023 04:08 AM
need some guidance in Excel. We have standard Excel format in office and customer need to update the data in the same format however they overwrite the drop down list by copy pasting the data. How to protect or stop copy paste on drop down column but select from drop-down list. I tried using protect sheet function but then drop-down function cannot be selected and save. Also VBA coding is not allowed