| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL97 - Log use of paste function in workbook
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
On Aug 1, 3:27 pm, "Kai Smith" wrote:
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 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.... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 "Kai Smith" wrote in message . uk: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 "KLZA" wrote in message ups.com: On Aug 1, 3:27 pm, "Kai Smith" wrote: 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 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.... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
On Aug 1, 5:00 pm, "Kai Smith"
wrote: 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 "KLZA" wrote in message ups.com: On Aug 1, 3:27 pm, "Kai Smith" wrote: 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 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....- Hide quoted text - - Show quoted text - Just kidding. :) |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
XL97 - Log use of paste function in workbook
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| LEN function in XL97 | Excel Discussion (Misc queries) | |||
| I cannot paste from one workbook to another. Copy works, paste do. | Excel Discussion (Misc queries) | |||
| Function in xl2003 not working in xl97 | Excel Discussion (Misc queries) | |||
| Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
| XL97: Out of stack space opening web page as workbook | Excel Programming | |||
Linear Mode
