Help required for copy-pasting columns.

  • Thread starter Thread starter BoredwithExcel
  • Start date Start date
B

BoredwithExcel

Hi there,
i want to prevent users from pasting more than one column at a time in
a sheet.

So I put in the following code.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


If InStr(1, Target.Address, ":", vbBinaryCompare) Then
If Sheet1.Range(Target.Address).Columns.Count > 1 Then
MsgBox "please copy paste one column at at time. our
developers are getting tired of this crap"
Exit Sub
End If
End If
End Sub



However the trouble with this is that it doesn't allow me to select
multiple columns from the sheet.

How do I distinguish between pasting and selecting?

Thanks in Advance
 
Not tried this but you should be able to test for

Application.CutCopyMode

If it's false allow it, if it's true disallow

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
There are no false / true values for this. The only permissible values
for cutcopymode are xlCopy and xlCut.
 
And you tested that did you?

Sub Test()
ActiveCell.Copy
MsgBox Application.CutCopyMode
Application.CutCopyMode = False
MsgBox Application.CutCopyMode
End Sub

First message box returns 1 (true), second message box returns 0 (false)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Hi Nick,
We are currently using Office 2000. Do you suppose that could be the
reason behind this?

Yes I tested this and it throws up a compile time error.
I am puzzled at this.

Cheers,
Abhijit
 
Abhijit

There should be no material difference. What error are you getting with the
code I provided and where does it error?

You should be able to establish, via this route whether Excel is trying to
Copy or Cut. You can then further look into whether it is trying to xlCopy
or xlPaste.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Hi Nick,
I have the following code in my open workbook event, but it doesn't
work.

Private Sub Workbook_Open()
Application.CutCopyMode = False
End Sub

Cheers,
Abhijit
 
Back
Top