Restrict Pasting Into A column

[elenakotanchyan]

Hi,

I am providing clients with feedback on their reports. I send them excel file where they input their response in one column. I have noticed that they copy and paste some of my responses instead of just typing out their own response. I was able to deactivate copy and paste on the entire worksheet but I was wondering if there is a way to use VBA code to deactivate pasting into a specified cell range (in my case E6:E504) and not the entire workbook/worksheet?

Thank you!

 

[Joe4]

Welcome to the Board!

You could create automated VBA code that would undo the last action whenever they try to update a cell in the range E6:E504 (will work for manual updates, or copy/paste).
To apply code like that, go to the sheet you wish to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    
'   See if any cells updated in range
    Set rng = Intersect(Range("E6:E504"), Target)
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "You are not allowed to update range E6:E504"
    End If
        
End Sub

Then try to make changes to the range E6:E504 and see what happens.

The only caveat with this is that they need to enable VBA/Macros on the workbook in order for the code to run.

 

[elenakotanchyan]

Welcome to the Board!

You could create automated VBA code that would undo the last action whenever they try to update a cell in the range E6:E504 (will work for manual updates, or copy/paste).
To apply code like that, go to the sheet you wish to apply it to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
   
'   See if any cells updated in range
    Set rng = Intersect(Range("E6:E504"), Target)
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "You are not allowed to update range E6:E504"
    End If
       
End Sub

Then try to make changes to the range E6:E504 and see what happens.

The only caveat with this is that they need to enable VBA/Macros on the workbook in order for the code to run.

Hi,

Thank you so much for your response! This code works.

However, this disables any kind of changes to the range. I want to only disable pasting into that range and leave the ability to manually type in the text. Do you know how I should do it?

Appreciate your help!

 

[Joe4]

OK, I stole some code from here to amend my original code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim UndoList As String
   
'   See if any cells updated in range
    Set rng = Intersect(Range("E6:E504"), Target)
   
'   Exit if no cells in watched range update
    If rng Is Nothing Then Exit Sub
   
'   Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
   
'   See if last action was paste
    If Left(UndoList, 5) = "Paste" Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        Application.CutCopyMode = False
        MsgBox "You are not allowed to paste to range E6:E504"
    End If
       
End Sub

 

[elenakotanchyan]

OK, I stole some code from here to amend my original code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim UndoList As String
  
'   See if any cells updated in range
    Set rng = Intersect(Range("E6:E504"), Target)
  
'   Exit if no cells in watched range update
    If rng Is Nothing Then Exit Sub
  
'   Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
  
'   See if last action was paste
    If Left(UndoList, 5) = "Paste" Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        Application.CutCopyMode = False
        MsgBox "You are not allowed to paste to range E6:E504"
    End If
      
End Sub

This is awesome! Just what I need! I really appreciate your help!

 

[Joe4]

You are welcome.
Glad I was able to help!