Hi guys
Im hoping someone can help me tweak some existing code to fulfill the description in the title. Essentially I've built a spreadsheet for a colleague that he can use to track data YTD by inputting new new data each week.
On a couple of occasions he has advised me that he's 'broken' the tracker and each time its because he's either using drag&drop or cut&paste within the workbook itself and the referencing on the other cells updates to follow the data (despite the fact that i've bult it with absolute referencing).
Naturally in order to use the tracker, he will still need to be able to use Paste Special to input the values whilst protecting the formatting of the spreadsheet,
I did some searching and found the below code to toggle the unwanted funtions, but this code does include toggling Paste Special as well. Ive tried removing the obvious references to Paste Special, but it still remains greyed out as a menu option.
Any help from you geniuses would be greatly appreciated! I've been slowly wrapping my head around VBA, but I think it's these Boolean expressions that might be throwing me this time :D
Thanks in advance guys
Im hoping someone can help me tweak some existing code to fulfill the description in the title. Essentially I've built a spreadsheet for a colleague that he can use to track data YTD by inputting new new data each week.
On a couple of occasions he has advised me that he's 'broken' the tracker and each time its because he's either using drag&drop or cut&paste within the workbook itself and the referencing on the other cells updates to follow the data (despite the fact that i've bult it with absolute referencing).
Naturally in order to use the tracker, he will still need to be able to use Paste Special to input the values whilst protecting the formatting of the spreadsheet,
I did some searching and found the below code to toggle the unwanted funtions, but this code does include toggling Paste Special as well. Ive tried removing the obvious references to Paste Special, but it still remains greyed out as a menu option.
Code:
'*** In a standard module ***
Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub
Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub
'*** In the ThisWorkbook Module ***
Option Explicit
Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub
Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub
Any help from you geniuses would be greatly appreciated! I've been slowly wrapping my head around VBA, but I think it's these Boolean expressions that might be throwing me this time :D
Thanks in advance guys