Remap "Paste" to PasteSpecial xlFormulas for unpro

[ACommandLineKindaGuy]

Anyone have a way to do this; presumably in a Workbook Selection Change sub?

The idea is to permit copying to unprotected cells without changing the cell format.

 

[Barry Katcher]

Don't know if you can re-map, but try this: go to Tools>Customize. In the dialogue box that appears, choose the Commands tab and then Edit. There are several Paste Special icons that you can drag up to the toolbar, such as Paste Values and Paste Formatting. There is also a Paste Special text icon that will open a box to select all of the special options.

In your case, if you don't want to disturb the formatting of the cells you're pasting to, you could use the Paste Values icon.

 

[Banker1]

Not sure I understand what your looking for but, the paste special syntax similar to this:

Range("A1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlPasteSpecialOperationNone, SkipBlanks:=False _
, Transpose:=False

You'd obviously change Range("A1") to the range that your pasting to. I'm thinking you can create a custom button on the toolbar and assign a macro to it using which uses a combination of Paste Special and Activecell

 

[ACommandLineKindaGuy]

Sorry--I haven't made myself clear. I want the spreadsheet to determine if someone is trying to paste something into an unprotected cell, and if so activecell.pastespecial paste:=xlpasteformulas. I'm assuming that there is no way to remap or "reroute" the paste command to pastespecial.

The whole purpose is to stop users from messing up the format of cells that they can input data into. Maybe there's a more global and better way to do this?

 

[Tazguy37]

You might be able to gray out the paste options on the toolbars, so that they can only use paste special. Some users may not like that, and may not know that functionality (EDIT: By "that functionality," I am referring to Paste Special) even exists...

 

[Justinlabenne]

How is your user pasting the formulas in? by Right-Clicking / Ctrl + V, Edit > Paste Special?

This is something I Use to remove the Paste and Paste Speical options from the Right-Click menu and substitute my own Paste Formulas (or values) menu items:

Option Explicit
Const mszBarName As String = "cell"
Const mszMenuCaption As String = "Paste &Formulas"


Sub CreateFormulaSpecial()
'   Add a custom menu item
    Const szMenuAction As String = "CstmPasteFormulas"
    Const lMenuFaceID As String = 31
    
    InitSKeys
    DeleteFormulaSpecial
    
    Dim cBar As CommandBar
    
    Set cBar = Application.Commandbars(mszBarName)
    
    On Error Resume Next
    With cBar
        .Controls(mszMenuCaption).Delete
        .Controls("&Paste").Delete
        .Controls("Paste &Special...").Delete
        
        With .Controls.Add(msoControlButton, , , 3, True)
        
            .Caption = mszMenuCaption
            
            .OnAction = szMenuAction
            
            .FaceId = lMenuFaceID
            
        End With
        
    End With

    Set cBar = Nothing
End Sub


Sub DeleteFormulaSpecial()
'   Remove the custom menu
    On Error Resume Next
        With Application
            '.Commandbars(mszBarName).Controls(mszMenuCaption).Delete
            .Commandbars(mszBarName).Reset
            .OnKey "^v", ""
        End With
    On Error GoTo 0
End Sub


Sub CstmPasteFormulas()
    On Error Resume Next
    If Not Selection.Locked Then
        Selection.PasteSpecial xlFormulas
    Else
        MsgBox "You cannot paste into protected cells", 64
    End If
    On Error GoTo 0
End Sub


Sub InitSKeys()
    Application.OnKey "^v", "CstmPasteFormulas"
End Sub

 

[ACommandLineKindaGuy]

Very nice, Justin--thanks!

I put your code in a module and added the following bto ThisWorkBook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

If Not Selection.Locked Then CreateFormulaSpecial Else DeleteFormulaSpecial

End Sub