Protected Excel Sheet acting up when Pasting multiple rows

[TheSmileyCoder]

This is one of those questions I fear will never be answered, but I have to give it a go.

I use an excel spreadsheet (built from Access) to export our review Comments. All of the columns are locked, bar one, in which our contractor is expected to put in his/her replies to our comments. Now sometimes they answer in word instead.

So I went to word, copied the relevant column and went into the excel file to paste their replies in. (Because I have a function in Access that imports their answers from the excel sheet).

Now for some reason the X rows in the word file becomes X+Y rows in the excel file. I presume it has something to do with linebreaks. That is not really my question, I simply list it for reference.

However, after pasting this into the unlocked column, it seems that it suddenly becomes locked. My own guesses are that it could be due to ranges being displaced, but to be honest im quite at a loss, as excel is not really my thing. Any idea why it becomes locked? And how to prevent it!

[Mariostg]

Hi Smiley.
Do you Edit->Paste Special -> As text, to make sure no word format is brought it?

[TheSmileyCoder]

Hi Mario

thank you for your suggestion. I have tried that. It does solve the Protected problem, but the paste output is still several rows longer then the word input.

I would hope to find the root cause of this, hopefully such that the end user can do the paste himself without needing special instructions. The end user is not all that tech savy.

[NeoPa]

Smiley:
This is one of those questions I fear will never be answered, but I have to give it a go.

Oh ye of little faith!

As Mario says, Pasting-Special Unformatted Text should resolve that for you. Protecting of cells in Excel is a two-level process.

1. Each cell has a property that specifies whether or not that item is considered locked or otherwise.
2. Worksheets, though, have a Protection setting which determines whether that property should even be considered.

Assuming that the worksheets are all protected (otherwise no locking would work at all), the column you're pasting into must be made up exclusively of cells which are unlocked. Fine so far.

Now, when you paste data in using the standard Paste process, you are pasting over the existing cells. Not just their values (as would be the case with Paste-Special). So, either a cell in a Word table also has a property which is matched on pasting with the Excel cell's Locked property - and thus replaces/overwrites it, or not, and the default for the worksheet is used (which typically means new cells are treated as Locked). What never happens is that the original setting is kept as is, as the whole cell has been replaced - not just the value. This is actually quite a common problem and, AFAIA, has not yet been resolved by a protection option that allows protection of the Locked property itself. A straightforward and, pretty obvious, solution TBF. The default for the worksheet can be changed, but pasting just the values will also work fine to ensure the original property values are maintained.

To break down the extra cells problem I would need to have access to the exact details more closely. If you want to send me an example I'll happily look into it for you. Otherwise we can talk about it on Skype. I'm pretty confident that it somehow comes down to how much the operator selects when they do the copying though. That's fairly clear in Excel, but can be less obvious in a Word Table.

[TheSmileyCoder]

So, back from holiday.

The main issue here is the mucking up of the locked property, since I can imagine it causes alot of confusion. If you can suggest a way to avoid that (by perhaps elaborating on how to change the default for the locked property) it would be very nice.

I will try to get a sample created which doesn't contain sensitive data to upload.

[NeoPa]

Unlocked cells allow pasting of cells from the clipboard Smiley. If those cells are themselves locked, and the worksheet itself is protected, then you no longer have unlocked cells. That's the problem in a nutshell. If you want to avoid the problem (unless and until MS fix the obvious flaw in their logic) then you'll need to disable pasting into those cells entirely and somehow manage handle transferring data only after testing it for validity. Not remotely trivial.

[TheSmileyCoder]

The cells being pasted from are not locked.

Could there be a way to set the default paste method for a worksheet to be text only?

[NeoPa]

Smiley:
The cells being pasted from are not locked.

Do you mean that? Or are you saying the sheet they were copied from is not protected? I explained the difference in detail in post #4. If they're really not locked then you shouldn't see any problem ;-)

Smiley:
Could there be a way to set the default paste method for a worksheet to be text only?

No. That would be too easy. There is no easy way around this that I've ever found, and I've looked. I found a way round for my needs, but it wasn't easy, and had more kludginess to it than I would have liked.

[TheSmileyCoder]

Pasting just the values does not trigger the cells becoming locked. As far as I am able to see the word table is not locked. I even tried creating a fresh word table, with a few rows in a fresh table and pasted from there.

The excel sheets (in their protected state with columns A through G locked, and H unlocked) gets distributed to the contractor (who ships some of to sub-contractors). I imagine they write their replies in another program, probably word, and when they paste the values into the excel sheet they complain of it being locked.
Now these excel sheets may go off to as many as 10 different companies and be handled by maybe 100 different people. So I would really like something that works, without having to give the receivers(s) special instructions, since I know they wont read them anyway.

[NeoPa]

Smiley:
So I would really like something that works, without having to give the receivers(s) special instructions, since I know they wont read them anyway.

Of course you would my friend (and so would many of us). Unfortunately ... (See earlier replies - particularly post #4).