PDA

View Full Version : How to disable Paste Special->Keep Source Formatting - Excel 2010



tromba
06-11-2012, 12:48 AM
Hi, I'm having a problem with getting function "Keep Source Formating" ( I need to paste some data from other application ) disabled when pasting data from other source, the problem is I cannot find which FindControl ID is responsible for option Paste Values - > "Keep Source Formatting".
Below is the code which I've developed ( I've managed to block cut/copy/paste but not the source formatting).
Also any other ideas how to disable this are more than welcome : )

Sub CutCopyPaste(Allow As Boolean)
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) 'paste special


With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
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()
MsgBox "Cut/Copy/Paste disabled!"
End Sub