Disabling all paste functions works differently in Excel 2007, so
you'll have to do a version check to modify ribbons. Here are two
things I've done previously, one for Open and one to reverse it to
Close.


Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.OnKey "^V"
.OnKey "^v"
.OnKey "^c"
.OnKey "^C"
.OnKey "^x"
.OnKey "^X"
.OnKey "^{DEL}"
.OnKey "^{DELETE}"
.OnKey "^{INSERT}"
.OnKey "+{DEL}"
.OnKey "+{DELETE}"
.OnKey "+{INSERT}"
.CommandBars("Cell").Enabled = True
.CommandBars("Row").Enabled = True
.CommandBars("Column").Enabled = True
With .CommandBars("Edit")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
.Controls("Paste Special...").Enabled = True
End With

With .CommandBars("Standard")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
End With

With .CommandBars("Cell")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
.Controls("Paste Special...").Enabled = True
End With

With .CommandBars("Column")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
.Controls("Paste Special...").Enabled = True
End With

With .CommandBars("Row")
.Controls("Copy").Enabled = True
.Controls("Cut").Enabled = True
.Controls("Paste").Enabled = True
.Controls("Paste Special...").Enabled = True
End With
End With
End Sub

Private Sub Workbook_Open()
With Application
.OnKey "^V", ""
.OnKey "^v", ""
.OnKey "^c", ""
.OnKey "^C", ""
.OnKey "^x", ""
.OnKey "^X", ""
.OnKey "^{DEL}", ""
.OnKey "^{DELETE}", ""
.OnKey "^{INSERT}", ""
.OnKey "+{DEL}", ""
.OnKey "+{DELETE}", ""
.OnKey "+{INSERT}", ""

With .CommandBars("Cell")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With

With .CommandBars("Column")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With

With .CommandBars("Row")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With

With .CommandBars("Edit")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
.Controls("Paste Special...").Enabled = False
End With

With .CommandBars("Standard")
.Controls("Copy").Enabled = False
.Controls("Cut").Enabled = False
.Controls("Paste").Enabled = False
End With
End With
End Sub



On Aug 3, 3:26 am, "Kai Smith"
wrote:
Thanks very much for your feedback, Peter. Very informative and it's
given me more to think about.

Kai

"Peter T" <peter_t@discussions wrote in message

:



Now, what I'd like to do is keep the paste buttons in the toolbar and
edit menu (and right click menu) enabled, but run the same error
loggin
routine when they're clicked. Is there a way to do this?

Not in XL97. In later versions you could by trapping the buttons' click
events. Only way would be to replicate the paste menus with your own
similar
looking and located buttons assigned to your own macro (which would both
paste and log), quite a lot of work.

Your 'controls' code is not reliable, the buttons might not be in the
order
you think they are, use FindControl. Also what about the paste controls
in
these popup commandbars - Cell, Row, Column.

Pasting is a problem as the sheet is protected, but allows data entry.
People typically paste a cell from another worksheet that is already
protected from editing. Hey-presto, as the current sheet is
protected,
it won't allow anyone else to enter data into that cell.

It took a while for this to sink in, but I get it now. I take it paste
values is not a problem, only formats (ie locked cells). Why not
devise
some code that when your protected sheet is activated it checks your
data
entry cells (a named range) are not locked. If .locked = Null (mixed) or
true, unprotect the sheet, unlock, reprotect.

Similar code could check the locked state when about to close and 'log',
ie
report which user has been using Paste. As no harm is done (the code can
unlock the data entry cells) perhaps a simple message to user that Big
Brother is watching !

Regards,
Peter T

"Kai Smith" wrote in message
.uk...

Thanks very much Peter, it didn't click that I had the macro in the
worksheet, rather than a module!

I have the current code in my spreadsheet now:

Private Sub Workbook_Activate()
Application.CommandBars("Edit").Controls(5).Enable d = False
Application.CommandBars("Edit").Controls(6).Enable d = False
Application.CommandBars("Standard").Controls(9).En abled = False
Application.CommandBars("Standard").Controls(10).E nabled = False
Application.OnKey "^v", "enterinlog"
Application.CommandBars("Toolbar List").Enabled = False

End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Edit").Controls(3).Enable d = True
Application.CommandBars("Edit").Controls(4).Enable d = True
Application.CommandBars("Edit").Controls(5).Enable d = True
Application.CommandBars("Edit").Controls(6).Enable d = True
Application.CommandBars("Standard").Controls(7).En abled = True
Application.CommandBars("Standard").Controls(8).En abled = True
Application.CommandBars("Standard").Controls(9).En abled = True
Application.CommandBars("Standard").Controls(10).E nabled = True
Application.OnKey "^v"
Application.CommandBars("Toolbar List").Enabled = True
End Sub
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal
Target As Excel.Range, Cancel As Boolean)
Cancel = True
End Sub

Private Sub enterinlog()
Sheets("Sheet2").Rows("1:1").Insert Shift:=xlDown
Sheets("Sheet2").Rows("1:1").Range("A1").Value =
Application.UserName
Sheets("Sheet2").Rows("1:1").Range("B1").Value = DateTime.Now
Sheets("Sheet2").Rows("1:1").Range("C1").Value = "PASTE FUNCTION
USED BY USER, PLEASE ADDRESS"
End Sub
<<thanks very much for your suggestion on this by the way

So, this disables right clicking for paste, and also the paste buttons
in the Edit menu, right click menu and standard toolbar.
It also logs the use of Ctrl+V for pasting.

Now, what I'd like to do is keep the paste buttons in the toolbar and
edit menu (and right click menu) enabled, but run the same error
loggin
routine when they're clicked. Is there a way to do this?

Pasting is a problem as the sheet is protected, but allows data entry.
People typically paste a cell from another worksheet that is already
protected from editing. Hey-presto, as the current sheet is
protected,
it won't allow anyone else to enter data into that cell.

Thanks very much

"Peter T" <peter_t@discussions wrote in message
:

Those OnKey's work fine for me, no idea where the "macro 1" comes
from.
I
assume your Sub enterinlog() is in a normal module.

Even if you trap use of keyboard to paste, what about all the
paste's on
various toolbars, have you disabled or substituted those with
similar
captions looking icons on your own menus, in the right positions of
course..

Why is paste such a problem, surely that's a normal operation. If
user
is
pasting over multiple cells that would be very easy to trap, if not
allowed.

In passing, in your enterinlog routine there's no need to select or
activate, anything so your Log sheet could be hidden or
xlVeryHidden.

Regards,
Peter T

"Kai Smith" wrote in message
.uk...

Sorry, forgot to include the code I already have...

Private Sub Workbook_Activate()

Application.OnKey "^v", "enterinlog"

Application.OnKey "+{INSERT}", "enterinlog"

Application.CellDragAndDrop = False

Application.OnDoubleClick = "Dummy"

End Sub

Private Sub Workbook_Deactivate()

Application.OnKey "^v"

Application.OnKey "+{INSERT}"

Application.CellDragAndDrop = True

Application.OnDoubleClick = ""

End Sub

Sub enterinlog()

Sheets("Log").Select

Rows("1:1").Select

Selection.Insert Shift:=xlDown

Range("A1").Select

ActiveCell.FormulaR1C1 = Application.UserName

Range("B1").Select

ActiveCell.FormulaR1C1 = DateTime.Now

Range("C1").Select

ActiveCell.FormulaR1C1 = "PASTE FUNCTION USED"

Sheets("Sheet1").Select

End Sub

Whenever I hit Ctrl+V, an error comes up saying "The macro '1'
cannot

be

found.". It doesn't log the paste function and where is it
getting
macro 1 from???

Thanks

<snip- Hide quoted text -

- Show quoted text -