Protected Sheet not protected from Copy Paste

**twistedsymphony** · 04-14-2010, 03:55 PM

I'm writing an import task in SQL Server that grabs data from an excel spread sheet created by one of my users (Access 2003).

SQL Server is quite picky about formatting, column names, and sheet name and since my users were having problems not changing these elements of the work sheet I created a password protected sheet that locks the column headers and formatting.

on the list of unprotected elements only the first two boxes are ticked.

When the user types things in manually this works fine. however if they copy data from another excel file that has custom formatting on the cells and paste it into my protected sheet it ignores the protection and changes the formatting for those cells!

This can't possible be the designed functionality of a protected sheet, is there any way to force the sheet to retain it's formatting and ignore the formatting of pasted content?

**Paul** · 04-14-2010, 05:21 PM

Hi Twisted, welcome to the forum.

When you protected the sheet, were the cells you want protected Locked? Check that, as I can't paste into a protected cell or range of cells whether I copy cells from the same workbook/sheet or a different one (seperate Excel.exe running).

Right-click on the affected cells and choose Format Cells. Look at the Protection tab and make sure 'Locked' is checked.

**twistedsymphony** · 04-15-2010, 08:24 AM

the only fields that are locked are the title row.

the remainder of the cells are not locked.

I want the user to be able to enter data into the cells below the title row, but NOT modify the formatting.

as I said this works for users entering text manually (the are also unable to change the formatting though any of excels menus) but it does not work when pasted from another spread sheet.

I've attached the file so you can see for yourself.

all of the fields are set to be formatted in plain text and it cannot be changed,

however, if you copy and paste a formatted cell from another sheet it will overwrite the formatting on the protected sheet.

**ChemistB** · 04-19-2010, 04:24 PM

It's not so easy. :/ Here's a thread where I asked the same question with some solutions.
http://www.excelforum.com/excel-prog...aste-code.html

**Paul** · 04-19-2010, 05:37 PM

I opened your sample workbook, then opened a second workbook with some text and formatting. Upon copying cells from the second workbook and pasting into the first (yours) I can paste the information into any of the unlocked cells, but not into the locked (row 1) cells. I get the "The cell or chart ... is protected.." message when trying to do that.