XL97 - Log use of paste function in workbook

  • Thread starter Thread starter Kai Smith
  • Start date Start date
K

Kai Smith

Hi there

I'm a self taught VBA user, but I have a problem I just can't get over.

Basically I have a workbook (obviously) but I don't want people to paste
into it. I only want them to enter data manually. Is there any way to
do two things:

a) LOG all uses of a paste function into the spreadsheet onto a separate
sheet?
b) Disable the paste function altogether.

I want to disable the function in due course, but as people have been
asked several times not to paste into the spreadsheet, I want to know
who the culprit is.

Any help would be greatly appreciated.

Thanks



Kai
 
Hi there

I'm a self taught VBA user, but I have a problem I just can't get over.

Basically I have a workbook (obviously) but I don't want people to paste
into it. I only want them to enter data manually. Is there any way to
do two things:

a) LOG all uses of a paste function into the spreadsheet onto a separate
sheet?
b) Disable the paste function altogether.

I want to disable the function in due course, but as people have been
asked several times not to paste into the spreadsheet, I want to know
who the culprit is.

Any help would be greatly appreciated.

Thanks

Kai
Click to expand...

Maybe you should get a video camera and mount it right behind the
"culprits". While you're at it, get Excel 2003 or 2007 and maybe a
motion sensor and an alarm system and and and....
 
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







Hi there

I'm a self taught VBA user, but I have a problem I just can't get over.

Basically I have a workbook (obviously) but I don't want people to paste
Click to expand...
into it. I only want them to enter data manually. Is there any way to
Click to expand...
do two things:

a) LOG all uses of a paste function into the spreadsheet onto a separate

b) Disable the paste function altogether.

I want to disable the function in due course, but as people have been
Click to expand...
 
Thanks for your constructive response, however I won't know who the
culprits are without getting my logging code to work...

Also, while I prefer xl2003, I have to use 97 as that's what my office
uses and most things I've made in the past with 2003, don't work
properly on 97.

If anyone can offer some serious advice, I'd really appreciate it.

Thanks
 
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 said:
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
Click to expand...
<snip>
 
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).Enabled = False
Application.CommandBars("Edit").Controls(6).Enabled = False
Application.CommandBars("Standard").Controls(9).Enabled = False
Application.CommandBars("Standard").Controls(10).Enabled = False
Application.OnKey "^v", "enterinlog"
Application.CommandBars("Toolbar List").Enabled = False

End Sub

Private Sub Workbook_Deactivate()
Application.CommandBars("Edit").Controls(3).Enabled = True
Application.CommandBars("Edit").Controls(4).Enabled = True
Application.CommandBars("Edit").Controls(5).Enabled = True
Application.CommandBars("Edit").Controls(6).Enabled = True
Application.CommandBars("Standard").Controls(7).Enabled = True
Application.CommandBars("Standard").Controls(8).Enabled = True
Application.CommandBars("Standard").Controls(9).Enabled = True
Application.CommandBars("Standard").Controls(10).Enabled = 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
 
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?
Click to expand...

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.
Click to expand...

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
 
Thanks for your constructive response, however I won't know who the
culprits are without getting my logging code to work...

Also, while I prefer xl2003, I have to use 97 as that's what my office
uses and most things I've made in the past with 2003, don't work
properly on 97.

If anyone can offer some serious advice, I'd really appreciate it.

Thanks







- Show quoted text -
Click to expand...

Just kidding. :)
 
Thanks very much for your feedback, Peter. Very informative and it's
given me more to think about.

Kai
 
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
 
Back
Top