# Cell Becomes Locked After Paste in Excel



## NodakGuy (Sep 23, 2004)

I’m having a strange Excel problem that I’ve not seen before. (Setup: IBM T40 Laptop; Windows 2000 Pro; Office XP Pro; all Microsoft service packs installed.) When I copy text from outside Excel (e.g., from Word or IE) and then paste it (normal paste—not special paste) into an Excel cell that has word wrap enabled and cell protection not locked (whether sheet is protected or not), I lose word wrap and the cell now becomes locked! (BTW, this happens under Excel 2000, as well.) Yes, it works as it should with Paste>Special as well as double-clicking in the cell before pasting. But I’m attempting to make a data entry sheet for a computer-adverse client so they can copy snippets of text from other documents and paste it into Excel with a simple Ctrl-V, and without any macros. I had originally intended to have the entire spreadsheet protected and only the data entry areas unlocked. However, if the client inadvertently does a normal paste, the cell now becomes locked and the user can’t undo it, because of the sheet protection. Ideas? Thanks!


----------



## ferguka (Jun 21, 2006)

*Cell locking problems*

Did you ever get a resolution to this problem. I've got the exact problem lying in front of me now.

Cheers
Kev


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome to TSF.

I'm afraid I cannot replicate this problem. Please tell me *exactly* how you are doing the copy and paste (i.e. using the mouse or keyboard only etc). Is this a new clean workbook? Are there any macros already in the workbook? One thing that does spring to mind is a Worksheet Event that is being triggered when you paste the data. Other than that I'm a bit stuck at the moment.


----------



## yustr (Sep 27, 2004)

Glas,

If its any help, I was able to duplicate what Nodak saw. I opened a fresh spreadsheet, used my mouse to copy the word "test" from MSWord, and right click pasted it into cell A1. When I checked cell protection, the "Locked" box was checked. It said that locking had no effect unless the worksheet was protected - which it was not.

I tried it using ctrl-C and ctrl-V to the same result.

I'm using MSOffice Prof 2003

:4-dontkno


----------



## Glaswegian (Sep 16, 2005)

Doh! Helps if I read the original post properly!

*All* cells start out as 'Locked' by default. The 'Locked' box will only have a checkmark when you enter data into the cell (or paste into the cell). I think the only way around this may be some Worksheet Event code that would trigger as the user selected certain cells. Certainly doable, but I'm not sure if it would be suitable for both posters here.


----------



## ferguka (Jun 21, 2006)

Thanks for the replies so far guys.

The scenario is that we're collecting vacancy details from recruiters for displaying their vacancy on web pages.

We have set up a template protected worksheet to act as a form with specific unprotected cells awaiting input. Users cut/paste formatted text (primarily from Word) into each unprotected cell. The cells are 'general' format, wrap text set. We have instructed users to cut/paste into the fx toolbar to try and retain the original Word format (carriage returns, bullets etc) within the cell as much as possible. We have many incidents of users following these instructions exactly but the cut/paste operations result in the unprotected cell becoming locked - it also changes its format to text, wrap text becomes unset, alignment goes to bottom left, the cell borders also disappear and the font changes to the default.

We have tried to replicate this using exactly the same source Word document, the same template and several different cut/paste scenarios, although we haven't dabbled with paste special yet.

Is there any VBA code that might prevent tis from happening or is there something fundamental which we haven't considered?

Cheers
Kev


----------



## Glaswegian (Sep 16, 2005)

Hi Kev

AFAIK you would need code to do this. I don't think you can actually prevent it from happening as such, but you can re-format and unlock the cell etc as soon as the cell is used. This would need to be a Worksheet Change Event, you would specify the specific cells and write some code that would re-format etc the cell. Once the user changed the cell, the Event would run and make the changes.

Let me know if this is the sort of thing that might work for you. If you are still unsure, I can knock up a test workbook for you to have a look at first.


----------



## ferguka (Jun 21, 2006)

Yes, I think a worksheet change event would work. 

Interestingly, we managed to replicate the problem on a test template yesterday and it only seems to happen when you paste directly after selecting the cell - double-clicking in the cell or using the fx bar to paste into does not change the format.

We'd certainly like to take you up on your offer of a test sheet to point us in the right direction as regards the code.

Thanks


----------



## Glaswegian (Sep 16, 2005)

Hi Kev

I'll get something put together for you over the weekend. It will really just be an example of what you can do with a Change Event.

Just to let you know in case I don't post back in this thread for a while. :smile:


----------



## Glaswegian (Sep 16, 2005)

Hi

Attached is a sample workbook that demonstrates some simple Events. Open the book at sheet 1. There are 4 cells coloured yellow. Now copy some text and paste it to any cell. If you choose any cell but the yellow ones, nothing happens. If you paste to a yellow cell, the font will change, some colours will change and borders will be added etc. Bear in mind this is a simple example.

To view the code, right click on the sheet tab and select 'View Code'.

If you need further help with the code or an explanation of any lines of the code just post back.


----------



## ferguka (Jun 21, 2006)

*Thanks*

Iain,
Thanks for the example code. I have passed this to a colleague to further develop our workbook. I'll let you know if we have any further issues. :wave:


----------



## VC_User (Jul 25, 2008)

I know this is an old thread but we just ran into the problem for the first time. Our resolution is to right click, select paste special, and select text. The cells remain unlocked.


----------



## cesarherna (Aug 6, 2010)

Hello, Glaswegian

Hello, how can I see or download the Attached sample workbook that demonstrates some simple Events.

Thank you


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome.

Since this is an old thread, I no longer have the workbook in question. Is there something specific you are looking for?


----------



## cesarherna (Aug 6, 2010)

Hi,

I am trying to copy five cells from IE and paste them on a worksheet in Excel and the cell becames locked after I paste them. I have a protected sheet and I've unlocked cells that use for the paste, I would like to see your Attached sample workbook with the 4 cells yellow coloured solving the locked problem.

Here is your original post with the mentioned sample. 

"Hi

Attached is a sample workbook that demonstrates some simple Events. Open the book at sheet 1. There are 4 cells coloured yellow. Now copy some text and paste it to any cell. If you choose any cell but the yellow ones, nothing happens. If you paste to a yellow cell, the font will change, some colours will change and borders will be added etc. Bear in mind this is a simple example.

To view the code, right click on the sheet tab and select 'View Code'.


Thanks for any help.


----------

