Prev: Merge Two Spreadsheets into 1 new sheet...
Next: Search Cells
From: Nick S on 27 Jun 2005 06:23

Is it possible to disable the copy and paste function without protecting
sheets.

Thanks

Nick


--
Nick S
------------------------------------------------------------------------
Nick S's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16512
View this thread: http://www.excelforum.com/showthread.php?threadid=382437

From: dominicb on 27 Jun 2005 08:25

Good afternoon Nick S

This type of request can be tricky, in that it is very easy to disable
the menu, but there are keyboard shortcuts to consider and the
rightclcik menu to worry about, so the easiest way is to introduce a
short single command macro into the ThisWorkbook that will empty the
clipboard every time an active cell changes:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
Application.CutCopyMode = False
End Sub

This will do the trick, by allowing a user to copy, but not paste. Add
this and it will prevent a user pasting something outside the current
sheet:

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CutCopyMode = False
End Sub

The downside : this code relies on the user opting to run the macros on
the sheet opening, if they say no to the "Run macros" question the code
will be useless.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18932
View this thread: http://www.excelforum.com/showthread.php?threadid=382437

From: DM Unseen on 27 Jun 2005 09:18
Nick,

you can disable/control key shortcuts like CTRL+c etc with
Application.Onkey function by e.g. checking sheetname first before
allowing copying.

You could assign new macro's to Cut/copy paste buttons or just add code
to them using event sinking(Excel 2000 and later). Especially sinking
the events of all the cut/copy/paste buttons should be considered
advanced VBA.

some test code:

Public Sub nocopy()

If ActiveSheet.Name = "Sheet1" Then
MsgBox "nocopy"
Else
Application.CommandBars.FindControl(, 21).Execute ' execute
copy button
End If

End Sub

Sub test()
Application.OnKey "^c", "nocopy"
End Sub

DM Unseen

From: Nick S on 27 Jun 2005 09:49

That does the trick


--
Nick S
------------------------------------------------------------------------
Nick S's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16512
View this thread: http://www.excelforum.com/showthread.php?threadid=382437

 | 
Pages: 1
Prev: Merge Two Spreadsheets into 1 new sheet...
Next: Search Cells