Copy and Pasting locks my unlocked cells

[travisc]

I have a program that generates a generic spreadsheet. When I copy the data from this sheet, and paste it in my "pretty" spreadsheet, it changes the formats in my sheet, and also locks the cells (Since the generic's cells are locked)... I protect all my sheets so other people at work can't modify any formulas or anything, so when it locks those cells, i have to unprotect the sheet to delete them.

What I need to know is how can I NOT allow copy and paste to change my cell formats? I already "Protect" my work sheets.

 

[Norie]

Can we see the code?

Perhaps you could use PasteSpecial to only copy the values.

 

[travisc]

When I say "program" I don't mean my own, it's a program my company uses. I have no code, just general cut & paste... I know I can use "Paste Special" --> Values... but when I have a lot of factory workers using this I don't want them to be able to screw it up by pasting new formats, know what I mean? I know many of them would just hit "Paste" and not paste special.

 

[bandit_1981]

Here are some subs that i use to diable paste and paste special and run my own version using paste values. You can also assign Cntrl V to the macro.

Sub AddItemToShortcut()
Dim NewItem As CommandBarControl
Dim FileCheck As Boolean
    RemoveItemFromShortcut
    Set NewItem = CommandBars("Cell").Controls.Add
    With NewItem
        .Caption = "Paste Values"
        .OnAction = "pastevalues"
        .BeginGroup = True
    End With
End Sub

Sub RemoveItemFromShortcut()
On Error Resume Next
    CommandBars("Cell").Controls("Paste Values").Delete
End Sub

Sub InitView()
    SetBars (xlOn)
    With CommandBars("Cell")
        .Controls("Paste").Enabled = False
        .Controls("Paste Special...").Enabled = False
    End With
End Sub

Sub ExitView()
    With CommandBars("Cell")
        .Controls("Paste").Enabled = True
        .Controls("Paste Special...").Enabled = True
    End With
End Sub

Sub pastevalues()
 On Error GoTo ERRORHANDLER
 Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 Application.CutCopyMode = False
Exit Sub
ERRORHANDLER:
    If err = 1004 Then
        Resume Next
    Else
        Debug.Print Error
        Resume Next
    End If
End Sub

[/code]