Urgent Please...Problem while pasting of data..

Posted by Rama on December 20, 2000 12:45 PM

Hi,

I would like to disable if the user is copying from a
different column. To be specific this the problem I am facing:

A B C
1 X Y P
2
3 X N M

Say Column A can take any text values.

say that the column B is associated with a drop-down with [Y,N] as values

Say that Colum C is also associated with a drop down with [M,N,P] as the values

Also all the cells under B and C have the drop-down properties set.

In this case, when a user selects A1, Then copy the contents( ctrl-C ). select B2 cell, and paste the data( ctrl-V ) this is what I have observed:

A B C
1 X Y P
2 X
3 X N M

* The drop-down property is gone for Cell B2, which I do not want to happen
* Also the value is not a correct one.

So I would like to know if there is a way disable this from happening.

Thanks in Advance.
Rama

Posted by GLITZ on December 20, 2000 6:53 PM

Maybe this will work:
When you paste don't use regular paste. Right click on the cell you want the information to be pasted in and do a "paste special".

From there do a paste "value"......

...Remember: Right-Click your buddy.

Posted by Tim Francis-Wright on December 21, 2000 7:55 AM

These might be reasonable workarounds:

To protect all but column A:-
put this in the Sheet object in question:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Overlap As Object
If Application.CutCopyMode &LT;&GT; False Then
Set Overlap = Intersect(ActiveSheet.Columns("A"), Target)
If Overlap Is Nothing Then
Application.CutCopyMode = False
Else
If Overlap.Address &LT;&GT; Target.Address Then
Application.CutCopyMode = False
End If
End If
End If
End Sub

To protect only columns B and C, use this instead:-

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Overlap As Object
If Application.CutCopyMode &LT;&GT; False Then
Set Overlap = Intersect(ActiveSheet.Columns("B:C"), Target)
If Not (Overlap Is Nothing) Then
Application.CutCopyMode = False
End If
End If
End Sub

You could change the ActiveSheet.Columns()
bit with ActiveSheet.Range("B2:C8") or whatever
range the inputs occupy if that makes more sense.

Good luck!

Posted by Rama on December 28, 2000 11:10 AM

Hi Tim,

THanks a lot for the help.
I have one more question:
In the above code:

Set Overlap = Intersect(ActiveSheet.Columns("B:C"), Target)

Only column B and C are protected.

But I need to dynamically set the range.

How do I achieve this?

Please help me.

THanks in Advance
Rama

Posted by Tim Francis-Wright on December 28, 2000 2:02 PM

The code will work as long as you set Overlap correctly. So, if previously in the routine
you set a range called MyRange,

Set Overlap = Intersect(MyRange, Target)
(I suspect that this will be the useful syntax,
because it's easy in VB to dynamically set
MyRange.)

Or, if you want to protect a range defined in
the worksheet itself,

Set Overlap = Intersect(Range("WBRange"), Target)

Good Luck!