Prevent copy and paste in one column

[Brian]

I am having trouble trying to prevent copying and pasting in one specific
column. The code refers to the specific range, but yet it prevents copying
and pasting on the whole worksheet.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Columns("H:H")) Is Nothing Then
Application.CellDragAndDrop = False
Application.CutCopyMode = False
Else
Application.CellDragAndDrop = True
End If

End Sub

 

[Gord Dibben]

Works for me in Excel 2003


Gord Dibben MS Excel MVP

 

[Brian]

I was thinking it would for me too.... but in 2007 it is not working. Well
correction, it is working too well, it prevents every column not just H....
any ideas?

 

[Gord Dibben]

Misread your original.

Thought you just wanted to prevent drag and drop in H

Try this simple revision to prevent pasting into H or drag and drop in H

Will not prevent copying from H

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 8 Then Exit Sub
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub


Gord