Prevent paste values in locked cells

Question

I have a sheet that has some locked cells and some input/unlocked cells. Because the input cells are formatted I only allow them to paste values (done this with a simple macro to paste values and assigned the control CTRL V) However, this macro let's me paste values in locked cells in which I have formulas that get overwritten.

If I do a copy paste special (ALT E + S + V) I am not allowed to paste if it affects the locked cells. The reason I need the macro with the assigned CTRL V to paste values only is because my users don't know how to copy paste special but only know the standard CTRL V, which in the case without macro destroys the format.

Any help on how I can restrict my CTRL V macro to only paste values to unlocked cells?

EDIT

The macro that I launch with CTRL V:

Sub PasteasValue() 
Selection.PasteSpecial Paste:=xlPasteValues 
End Sub

Answer 1

Without seeing your code, all we can give you is a snippet. Basically, you can test for whether it is locked with

ActiveCell.Locked

This will return a True if it is. Substitute your identification of the cell for ActiveCell.

Answer 2

I have found a solution here: Excel VBA code to Force Values Only Paste causes strange behavior when pasting objects

The below code placed in ThisWorkbook allows to copy paste normally and re-adjusts the formatting to the target worksheet specific format.

    Dim UndoString As String, srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
    If UndoString = "Auto Fill" Then
        Set srce = Selection
        srce.Copy
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
    Else
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Answer 3

An alternate approach is not to over-ride Ctrl-V but to use a context menu.

Within the worksheet that you want to control, place the following event handler:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim tHasLock As Boolean
    If IsNull(Target.Locked) Then    ' at least one locked cell
        tHasLock = True
    Else
        tHasLock = Target.Locked ' will only be true if the cell(s) are locked
    End If

    If tHasLock And Application.CutCopyMode <> False Then
        Cancel = True
        CommandBars("MyMenu").ShowPopup
    End If
End Sub

This will activate if there is something been cut or copied and the cell is locked. Otherwise you will get the normal menu.

Within the workbook you want to create "MyMenu". You can only do this once within the Application, and will have to do it each time you start the application. You could do this on Workbook_Open.

Private Sub CreateMenu()
Dim tMyMenu As CommandBar
Dim tMenuItem As CommandBarControl

    Set tMyMenu = Application.CommandBars.Add("MyMenu", msoBarPopup)
    Set tMenuItem = tMyMenu.Controls.Add(ID:=370) ' Standard Paste values menu item
    tMenuItem.Caption = "Paste &Values"

End Sub

What should happen is that this will allow the paste to happen in the locked cells.

On further testing - this does not paste as expected (I will be drafting a question about this myself for other purposes, because a special menu I have made for work does paste into locked cells!). An alternative is below.

Private Sub CreateMenu()
Dim tMyMenu As CommandBar
Dim tMenuItem As CommandBarControl

    Set tMyMenu = Application.CommandBars.Add("MyMenu", msoBarPopup)
    'Set tMenuItem = tMyMenu.Controls.Add(ID:=370) ' Standard Paste values menu item
    'tMenuItem.Caption = "Paste &Values"
    Set tMenuItem = tMyMenu.Controls.Add(msoControlButton)
    With tMenuItem
        .Caption = "Special Paste"
        .OnAction = "PasteasValue" '"MySpecialPasteMacroName"
    End With
End Sub

If you have already created the special menu and you try to create it again, then you will get a run-time error (run time error '5'). Just use Application.CommandBars("MyMenu").Delete and you will be good to go.