Unable to lock cell formatting

[perkin_warbeck]

When a user pastes data onto a worksheet, how can I prevent the pasted data from erasing the formatting (cell borders, background color, number formats, etc.)?

The following method (which I found in several places on the web) does not work:

1. Select all cells on the worksheet
2. Home tab -> Cells group -> Format -> Format Cells... -> uncheck "Locked" -> OK
3. Home tab -> Cells group -> Format -> Protect Sheet -> make sure "format cells" is unchecked" and do not enter a password -> OK (see screenshot below)

After doing this, the formatting groups in the Home tab are all grayed out (good).  However, when I paste into the sheet, the formatting in the pasted range is erased.  What am I doing wrong?

[#2]

@perkin_warbeck I don't know that you can get around that.  You could do it using VBA.  I don't know what sort of formatting you have but here is a sample of what you might do in VBA (I assume you still lock the worksheet to make sure they only paste where you want them to paste).  The following VBA would be added to the specific sheet you want to control in VBA.

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect
    Application.EnableEvents = False
    With Target
        .ClearFormats
        .Font.FontStyle = "Times New Roman"
        .Font.Bold = True
        .Interior.Color = RGB(250, 250, 0)
        .Locked = False
    End With
    Application.EnableEvents = True
    ActiveSheet.Protect
End Sub

[#3]

@mtarler My application is written entirely in VBA, and I am already handling Worksheet_Change events.  My understanding is that Worksheet_Change is called AFTER the change occurs.  In that case, the code would repair formats erased by the paste.  I will consider doing it that way, but it will be messy because the user might paste into several columns of data, and each column has a different background color.  I was hoping to avoid that.

[#4 · Accepted solution]

@perkin_warbeck  yes you are correct and I'm sure you read my sample code.  I delete any formats they brought in and then added the new formats.  I apply the new formats to all pasted cells but sounds like you have to be more selective.

How about a completely different approach where you have them paste the data in a different sheet and then you use VBA to copy and paste values only.

Or if they past in a different sheet then you just use a formula to reference that data from the sheet with the formats.

You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values or give them a pop-up.  You can lock the entire sheet but allow selection so they can paste anywhere but VBA would do the work instead.

There are many ways to skin a cat in excel, it's hard to suggest the best option for you without knowing more about the application.

[#5]

@mtarler I should have mentioned that I am very new to VBA, so I'm trying out most of these things for the first time.  I think you have pointed me in a a couple of good directions. 

Repairing the formatting is certainly one way to do it.  I don't mind the application being complex if it spares the user from having to remember to Paste Values. 

But you say "You can also use on selection in VBA to determine where they want to paste the data and then use VBA to paste values." It's an interesting solution, but it would require another button to accomplish the paste.  I'd like users to be able to paste normally.

I will mark your response as "Best Response" because you have given me several good ideas. Thanks!

[#6]

@mtarler I've been thinking more about your solution....  Repairing the formatting after a paste would be easier if each cell somehow "remembered" its own proper formatting.  This could be done by storing a duplicate of the original data area (with properly formatted cells) on a private worksheet.  Then Worksheet_Change would simply access the correct formatting from the private worksheet and apply it to the each cell of the target.

[#7]

@perkin_warbeck  If that is your "fixed" formatting that should be pretty easy to "hard code" into the VBA.  You idea of making a copy to a hidden sheet would work and as long as it isn't too big a file shouldn't cause too much delay.  Here is an example of my last suggestion above where you lock the entire sheet and just paste the data where ever they click.  It isn't "perfect" but could work for you:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const noPasteCell = "$A$1"
    confirm = MsgBox("Paste your data here?", vbYesNo)
    On Error GoTo fail
    If confirm = vbYes Then
        Application.EnableEvents = False
        ActiveSheet.Unprotect
        Target.PasteSpecial (xlPasteValues)
        ActiveSheet.Protect
    End If
fail:
    Range(noPasteCell).Select
    Application.EnableEvents = True
    If Err Then
        MsgBox ("Paste Failed.  Check your copy and try again.")
        Err.Clear
    End If
End Sub

I defined "noPasteCell" as A1 just as a place to auto move the selection to so it is intuitive for them to click where they want to paste the data.  You could add additional checks to make sure they aren't pasting data in incorrect areas.  You could also add a Password to prevent them from circumventing it if you wanted.

[#8]

@mtarler I just lost a long reply because "authentication failed" when I posted.  So I'll re-type an abbreviated version.  I decided to go with partial locking, rather than full sheet locking. Paste fails if the clipboard data would overlap onto locked cells, which is a very desirable behavior.  There is no partial paste.  Since Excel is protecting the worksheet, there is no need to take the extra precautions in your example. 

There are also controls on the worksheet (screenshot).  I unlocked these so they can be used when the sheet is protected.  Since those controls call macros that modify locked cells, the macros will have to temporarily unprotect the sheet.