Excel VBA Disable From Pasting Specific Range in Worksheet

Question

I have certain Excel macro that people paste into it data manually before the excution.

To avoid errors in the macro running I want to disable certain columns from pasting data in it.

I tried with

Private Sub Worksheet_Change(ByVal Target As Range) 
    Target.Column = 7 
    Target.Column = 8 
    Target.Column = 12 
End Sub

Can someone guide me how it is possible?

Yes, read about `Worksheet_Change` event, is someone tries to paste at column "B" (for this example), then inside check if `Target.Column = 2` , disable the paste procedure — Shai Rado, Oct 29 '17 at 06:55
lock the cells that you want to be off limits and protect the worksheet — jsotola, Oct 29 '17 at 07:11
@ShaiRado I see that I need to create private sub for this, but the pasting is **before** the macro running. Is there any way to control it without creating new sub? I want it will be active when the file get opened — Rafael Osipov, Oct 29 '17 at 07:37
you do not need VBA to lock cells and to protect a worksheet. those functions are done in the excel menus ... if you want to use VBA then record a macro — jsotola, Oct 29 '17 at 07:44
@ShaiRado Private Sub Worksheet_Change(ByVal Target As Range) Target.Column = 7 Target.Column = 8 Target.Column = 12 End Sub DIDNT WORK — Rafael Osipov, Oct 29 '17 at 08:19
Useful link: https://stackoverflow.com/questions/27818152/excel-vba-how-to-detect-if-something-was-pasted-in-a-worksheet — Robin Mackenzie, Oct 29 '17 at 08:31

score 4 · Accepted Answer · answered Oct 29 '17 at 08:27

Try the code below, add it in the worksheet you want to disable the user from pasting into certain columns.

Private Sub Worksheet_Change(ByVal Target As Range)

' Restrict the user from deleting or Pasting to certain columns

Select Case Target.Column
    Case 7, 8, 12
         With Application
            .EnableEvents = False
            .Undo
            MsgBox "Pasting to columns 'F' , 'G' or 'L' is not allowed", vbCritical
            .EnableEvents = True
        End With

    Case Else
        ' do nothing / or something else

End Select

End Sub

Excel VBA Disable From Pasting Specific Range in Worksheet

1 Answers1