Prev: Which textbox am I in?
Next: Picture Orientation
From: Brian on 30 Apr 2010 14:42
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
From: Gord Dibben on 30 Apr 2010 16:48
Works for me in Excel 2003


Gord Dibben MS Excel MVP

On Fri, 30 Apr 2010 11:42:01 -0700, Brian <Brian(a)discussions.microsoft.com>
wrote:

>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

From: Brian on 3 May 2010 16:46

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" wrote:

> Works for me in Excel 2003
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 30 Apr 2010 11:42:01 -0700, Brian <Brian(a)discussions.microsoft.com>
> wrote:
>
> >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
>
> .
>
From: Gord Dibben on 4 May 2010 11:31
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

On Mon, 3 May 2010 13:46:01 -0700, Brian <Brian(a)discussions.microsoft.com>
wrote:

>
>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" wrote:
>
>> Works for me in Excel 2003
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Fri, 30 Apr 2010 11:42:01 -0700, Brian <Brian(a)discussions.microsoft.com>
>> wrote:
>>
>> >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
>>
>> .
>>

 | 
Pages: 1
Prev: Which textbox am I in?
Next: Picture Orientation