Copy and Pasting locks my unlocked cells

travisc

Board Regular
Joined
Oct 19, 2005
Messages
135
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Can we see the code?

Perhaps you could use PasteSpecial to only copy the values.
 

travisc

Board Regular
Joined
Oct 19, 2005
Messages
135
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

Board Regular
Joined
Aug 17, 2005
Messages
201
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.

Code:
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

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

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

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

Code:
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,254
Members
412,313
Latest member
pauloalex
Top