How to avoid pasting a cell's locked property

[skysurfer]

Hi,

Is there a way when using copy/cut/paste to target cells that are unlocked to prevent those target cells from receiving the locked property setting of the source cells?

I have a protected sheet with 1,000s of unlocked cells meant for the user to enter or paste in content. The problem is the paste procedure not only inserts the source's values/formulas, but also their default "locked" property. So, after the paste is complete, the target cells that were supposed to stay unlocked are now locked and the user can no longer change them.

Lawrence

[Lifesigns]

Hi,

If you only want values they should paste special values. When using copy and paste as normal it copies everthing from the source to the destination.

This could also be achieved with VBA but it is a little tricky.

[skysurfer]

Lifesigns,

A little tricky? I can deal with a little tricky. I know just enough VBA to get myself into serious trouble.

Thing is, I can not force the user to use paste>special>values. So, I'm looking at a VBA solution. Hoping that it is at least possible?

what do you think?

Lawrence

Hi,

If you only want values they should paste special values. When using copy and paste as normal it copies everthing from the source to the destination.

This could also be achieved with VBA but it is a little tricky.

[Lifesigns]

It's certainly possible, I've used it before to avoid similar issues. Have to agree you cannot make people paste special values.

I think the easiest would be to set the cell protection properties from VBA, protecting & unprotecting worksheets is relatively straight forward.

Do you have a sample workbook to work from, it would be good see the layout. We would need to know exactly what range of cells should be protected.

[skysurfer]

Lifesigns,

The worksheet I want to apply this to is very basic, but the workbook is pretty large and full of boss's proprietary data. Anyway, all I need to do is make sure that every cell right of and below C16 remains Locked = False after pasting. That also includes C16 itself.

I did run across this code that I've been playing with (see attached). It protects the number format of one cell. It's pretty cool. But I can't seem to get it to work for a contiguous range of cells like I need, or to only make it work for cell protection.

I wonder even if we do get it to work whether it will be extremely slow when pasting 1,000s of cells??? Or whether you have something better.

:-D

[Leith Ross]

Hello skysurfer,

Since you have a contiguous range, this is very simple. Here is an example of how to lock all cells from C16 on.

Please Login or Register  to view this content.

[skysurfer]

Leith,

What triggers your macro? Any cell change or a format change?

The reason I ask is that I am concerned about the recalc time if there are 1,000s of cells in the range and only one cell is changed.

Thanks!!!

Lawrence

[Leith Ross]

Hello Lawrence,

The event will only fire when the user has changed the cell or it has been changed using VBA code. The recalculation event will not trigger the Worksheet_Change event.

[skysurfer]

Leith,

Is there a way to trigger the macro only when a paste is done? I tried your code and it works (THANKS!!!), but the range is so large--even when I chop it down to a few thousand rows--that the recalc time is about a half second. The user will not always paste data and I do not want to penalize her with that lag if she only hard enters some text or numbers.

Lawrence

[Leith Ross]

Hello Lawrence,

There is no specific event that is triggered for a Paste operation. It may be possible to reroute the command through your macro which would allow us to know when a paste was taking place. The menu commands can rerouted easy enough, but I will need to check on the Ctrl+V piece.

[shg]

... but I will need to check on the Ctrl+V piece.

Please Login or Register  to view this content.

[Leith Ross]

Hello Lawrence,

Still working on this. I can disable both the Paste and Paste Special menu items. the tricky part is the Control-V key strokes. While Application.OnKey can call another macro, pasting the information successfully is a more complicated matter. SendKeys will not work correctly which requires using the API. Currently, I am trying to code something with the API to make this work correctly. Here is the code to enable or disable the Paste and Paste special commands on the menus.

Please Login or Register  to view this content.

[skysurfer]

Hi guys,

Thanks for all the help!

It's not that I want to keep the user from pasting, just to keep the pasting from changing the locked property of the cells to locked. I'm guessing, but if we can detect when a paste command is executed (shg's Application.onKey?), then use Leith's code?

FYI... I tried experimenting with disabling pasting menu items and shortcut keys, but things got complicated when having the file be compliant with both xl2003 and 2010.

Lawrence

[Leith Ross]

Hello Lawrence,

Application.OnKey can run a macro when Ctrl+V is used, but it most likely will no work the way you want it to. This happens because this key combination is hooked into the operating system and not just Excel. the ability to paste text, formats, pictures, web links, etc. is very complex. It requires the system knowing about all these different objects and how to access them. In object oriented programming this is referred to as polymorphism and places these function in vary special class.

[shg]

I've no doubt that Leith is correct, but you might try this.

In ThisWorkbook:

Please Login or Register  to view this content.

In a code module:

Please Login or Register  to view this content.

If that doesn't work, there's a big blob of code at http://www.vbaexpress.com/kb/getarticle.php?kb_id=957

[skysurfer]

Leith,

I've got this thing figured out thanks to your help and shg's.

Only thing I need to resolve is how to replace Rows.Count in your code with a named range. I tried simply inserting:

Please Login or Register  to view this content.

but I get run time error 1004.

Lawrence

[skysurfer]

Okay, got it. Phew.

Just need to put the range name in brackets. No quotes. No Sheet.Range verbage.

Thanks again!

Lawrence