| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste)
I'm stuck!
I have a code (below) to disable the cut copy and paste features in a workbook. 1. from the edit menu 2. the actual comand buttons (the toolbar buttons) 3. ctr c etc 4. right click options. My prombem is that I get a runtime error 5 saying that this is an invalid proceedure. Any help on the bug here would be appreciated. Thanks Tim Private Sub Workbook_Activate() Application.CommandBars("Standard").Controls("Cut" ).Delete Application.CommandBars("Standard").Controls("Copy ").Delete Application.CommandBars("Standard").Controls("Past e").Delete Application.CommandBars("Formatting").Controls("Cu t").Delete Application.CommandBars("Formatting").Controls("Co py").Delete Application.CommandBars("Formatting").Controls("Pa ste").Delete Application.CellDragAndDrop = False Application.CutCopyMode = False Application.OnKey "^X", "" Application.OnKey "^C", "" End Sub Private Sub Workbook_Deactivate() Application.CommandBars(1).Reset Application.CellDragAndDrop = True Application.CutCopyMode = True Application.OnKey "^X", "^X" Application.OnKey "^C", "^C" End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste)
I may be missing somenting here but there
aren't "Cut", "Copy", "Paste" controls in the Formatting menu - at least not in my version of Excel. Also, in point 1 you say you disable the cut, copy and paste features from the Edit menu. However, your code doesn't do this. In point 4 you say you disable "right click options" but your code doesn't do this. Did you mean CommandBars("Cell") which is the right-click popup menu? Or did you think Application.CutCopyMode = False would disable these features? You also don't reset the Standard or Formatting tool bars in the Deactivate event. You only reset the Worksheet Menu Bar. Suggested is as follows. Note that I use the DragAndDropStatus variable to hold the state of the CellDragAndDrop option (True of False) and only return it to its original state in the Deactivate event. I don't believe your syntax was correct re the OnKey method. Note the curly brackets and lower case (user isn't likey to use upper case and lower case will work). It probably isn't necessary to reset the OnKey items because I believe they reset automatically on close (or open). I reset them anyway at least for demo purposes. Dim DragAndDropStatus As Boolean Private Sub Workbook_Open() With Application DragAndDropStatus = .CellDragAndDrop ..OnKey "^{x}", "" ..OnKey "^{c}", "" ..CellDragAndDrop = False With .CommandBars("Cell") ..Controls("Cut").Enabled = False ..Controls("Copy").Enabled = False ..Controls("Paste").Enabled = False End With With .CommandBars("Worksheet Menu Bar").Controls("Edit") ..Controls("Cut").Enabled = False ..Controls("Copy").Enabled = False ..Controls("Paste").Enabled = False End With End With End Sub Private Sub Workbook_Deactivate() With Application ..CellDragAndDrop = DragAndDropStatus ..CommandBars("Cell").Reset ..CommandBars("Worksheet Menu Bar").Reset ..CommandBars("Standard").Reset ..OnKey "^{x}" 'Probably not actually necessary ..OnKey "^{c}" 'Same End With End Sub Not rigorously tested and never used by me for any project. Regards, Greg VBA Amateur -----Original Message----- I'm stuck! I have a code (below) to disable the cut copy and paste features in a workbook. 1. from the edit menu 2. the actual comand buttons (the toolbar buttons) 3. ctr c etc 4. right click options. My prombem is that I get a runtime error 5 saying that this is an invalid proceedure. Any help on the bug here would be appreciated. Thanks Tim Private Sub Workbook_Activate() Application.CommandBars("Standard").Controls("Cut ").Delete Application.CommandBars("Standard").Controls ("Copy").Delete Application.CommandBars("Standard").Controls ("Paste").Delete Application.CommandBars("Formatting").Controls ("Cut").Delete Application.CommandBars("Formatting").Controls ("Copy").Delete Application.CommandBars("Formatting").Controls ("Paste").Delete Application.CellDragAndDrop = False Application.CutCopyMode = False Application.OnKey "^X", "" Application.OnKey "^C", "" End Sub Private Sub Workbook_Deactivate() Application.CommandBars(1).Reset Application.CellDragAndDrop = True Application.CutCopyMode = True Application.OnKey "^X", "^X" Application.OnKey "^C", "^C" End Sub . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste)
Thanks Greg...
Works just fine. I also tried the line ..Controls("Office Clipboard").Enabled = False to not allow the user to paste from the clipboard, as they can with this code but I get an error. Any other ideas? Thanks & Regards Tim Bray "Greg Wilson" wrote in message ... I may be missing somenting here but there aren't "Cut", "Copy", "Paste" controls in the Formatting menu - at least not in my version of Excel. Also, in point 1 you say you disable the cut, copy and paste features from the Edit menu. However, your code doesn't do this. In point 4 you say you disable "right click options" but your code doesn't do this. Did you mean CommandBars("Cell") which is the right-click popup menu? Or did you think Application.CutCopyMode = False would disable these features? You also don't reset the Standard or Formatting tool bars in the Deactivate event. You only reset the Worksheet Menu Bar. Suggested is as follows. Note that I use the DragAndDropStatus variable to hold the state of the CellDragAndDrop option (True of False) and only return it to its original state in the Deactivate event. I don't believe your syntax was correct re the OnKey method. Note the curly brackets and lower case (user isn't likey to use upper case and lower case will work). It probably isn't necessary to reset the OnKey items because I believe they reset automatically on close (or open). I reset them anyway at least for demo purposes. Dim DragAndDropStatus As Boolean Private Sub Workbook_Open() With Application DragAndDropStatus = .CellDragAndDrop .OnKey "^{x}", "" .OnKey "^{c}", "" .CellDragAndDrop = False With .CommandBars("Cell") .Controls("Cut").Enabled = False .Controls("Copy").Enabled = False .Controls("Paste").Enabled = False End With With .CommandBars("Worksheet Menu Bar").Controls("Edit") .Controls("Cut").Enabled = False .Controls("Copy").Enabled = False .Controls("Paste").Enabled = False End With End With End Sub Private Sub Workbook_Deactivate() With Application .CellDragAndDrop = DragAndDropStatus .CommandBars("Cell").Reset .CommandBars("Worksheet Menu Bar").Reset .CommandBars("Standard").Reset .OnKey "^{x}" 'Probably not actually necessary .OnKey "^{c}" 'Same End With End Sub Not rigorously tested and never used by me for any project. Regards, Greg VBA Amateur -----Original Message----- I'm stuck! I have a code (below) to disable the cut copy and paste features in a workbook. 1. from the edit menu 2. the actual comand buttons (the toolbar buttons) 3. ctr c etc 4. right click options. My prombem is that I get a runtime error 5 saying that this is an invalid proceedure. Any help on the bug here would be appreciated. Thanks Tim Private Sub Workbook_Activate() Application.CommandBars("Standard").Controls("Cut ").Delete Application.CommandBars("Standard").Controls ("Copy").Delete Application.CommandBars("Standard").Controls ("Paste").Delete Application.CommandBars("Formatting").Controls ("Cut").Delete Application.CommandBars("Formatting").Controls ("Copy").Delete Application.CommandBars("Formatting").Controls ("Paste").Delete Application.CellDragAndDrop = False Application.CutCopyMode = False Application.OnKey "^X", "" Application.OnKey "^C", "" End Sub Private Sub Workbook_Deactivate() Application.CommandBars(1).Reset Application.CellDragAndDrop = True Application.CutCopyMode = True Application.OnKey "^X", "^X" Application.OnKey "^C", "^C" End Sub . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste)
Thanks Ivan,
A great help. Regards Tim (Ivan F Moala) wrote in message m... Tim Try the routine here http://www.xcelfiles.com/VBA_Quick13.html (Tim) wrote in message . com... Thanks Greg... Works just fine. I also tried the line .Controls("Office Clipboard").Enabled = False to not allow the user to paste from the clipboard, as they can with this code but I get an error. Any other ideas? Thanks & Regards Tim Bray "Greg Wilson" wrote in message ... I may be missing somenting here but there aren't "Cut", "Copy", "Paste" controls in the Formatting menu - at least not in my version of Excel. Also, in point 1 you say you disable the cut, copy and paste features from the Edit menu. However, your code doesn't do this. In point 4 you say you disable "right click options" but your code doesn't do this. Did you mean CommandBars("Cell") which is the right-click popup menu? Or did you think Application.CutCopyMode = False would disable these features? You also don't reset the Standard or Formatting tool bars in the Deactivate event. You only reset the Worksheet Menu Bar. Suggested is as follows. Note that I use the DragAndDropStatus variable to hold the state of the CellDragAndDrop option (True of False) and only return it to its original state in the Deactivate event. I don't believe your syntax was correct re the OnKey method. Note the curly brackets and lower case (user isn't likey to use upper case and lower case will work). It probably isn't necessary to reset the OnKey items because I believe they reset automatically on close (or open). I reset them anyway at least for demo purposes. Dim DragAndDropStatus As Boolean Private Sub Workbook_Open() With Application DragAndDropStatus = .CellDragAndDrop .OnKey "^{x}", "" .OnKey "^{c}", "" .CellDragAndDrop = False With .CommandBars("Cell") .Controls("Cut").Enabled = False .Controls("Copy").Enabled = False .Controls("Paste").Enabled = False End With With .CommandBars("Worksheet Menu Bar").Controls("Edit") .Controls("Cut").Enabled = False .Controls("Copy").Enabled = False .Controls("Paste").Enabled = False End With End With End Sub Private Sub Workbook_Deactivate() With Application .CellDragAndDrop = DragAndDropStatus .CommandBars("Cell").Reset .CommandBars("Worksheet Menu Bar").Reset .CommandBars("Standard").Reset .OnKey "^{x}" 'Probably not actually necessary .OnKey "^{c}" 'Same End With End Sub Not rigorously tested and never used by me for any project. Regards, Greg VBA Amateur -----Original Message----- I'm stuck! I have a code (below) to disable the cut copy and paste features in a workbook. 1. from the edit menu 2. the actual comand buttons (the toolbar buttons) 3. ctr c etc 4. right click options. My prombem is that I get a runtime error 5 saying that this is an invalid proceedure. Any help on the bug here would be appreciated. Thanks Tim Private Sub Workbook_Activate() Application.CommandBars("Standard").Controls("Cut ").Delete Application.CommandBars("Standard").Controls ("Copy").Delete Application.CommandBars("Standard").Controls ("Paste").Delete Application.CommandBars("Formatting").Controls ("Cut").Delete Application.CommandBars("Formatting").Controls ("Copy").Delete Application.CommandBars("Formatting").Controls ("Paste").Delete Application.CellDragAndDrop = False Application.CutCopyMode = False Application.OnKey "^X", "" Application.OnKey "^C", "" End Sub Private Sub Workbook_Deactivate() Application.CommandBars(1).Reset Application.CellDragAndDrop = True Application.CutCopyMode = True Application.OnKey "^X", "^X" Application.OnKey "^C", "^C" End Sub . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy paste error | Charts and Charting in Excel | |||
| Filtered copy and paste error | Excel Discussion (Misc queries) | |||
| copy and paste error | Excel Discussion (Misc queries) | |||
| Copy/Paste error | Excel Worksheet Functions | |||
| Error in Copy/Paste | Excel Programming | |||
![Tim[_36_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
