CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

Excel VBA Disable From Pasting Specific Range in Worksheet

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?

Upvotes: 1

Views: 3820

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 4

Related Questions