The Paste Range address

[sonben]

Hi There,

I have a an issue that is proving quite difficult to solve. I have a data input sheet that has cell notes that I want to be preserved with the cell. That is if someone copies data from one cell to another I don't want the cell note to copy. So far I have managed this by overloading the paste functionality and have some logic which says - if there is a cell note, then Selection.PasteSpecial Paste:=xlPasteFormulas, otherwise Selection.PasteSpecial Paste:=xlAll. This is fine and works well.

I now have a second problem. The sheet is locked and the user can only paste into unlocked cells. VBA in its wisdom completely ignores the fact the sheet is protected and will paste values into locked cells even when the sheet is protected. This means the user may end up coping over a formula, and not be able to rectify the issue as the sheet is protected. Not good behaviour.

I have gone down the path of checking before pasting if the selection is locked or not. This would work except for the situation where they are copying say a 1x10 range, and then selecting a 1x1 range and pasting. In this case excel see the destination range being 1x10 (which may include some locked cells). The only information I have is the selection range being 1x1. I can not tell if the destination range is locked or not, so this is not a solution.

Questions are;
1. Is it possible to access the paste destination range (i.e. the 1x10 range) so I can check to query the locked status?
2. Possibly an alternative here - if I knew the dimensions of the copy range, then I could do a resize of the current selection and query that?
3. Is it possible to prevent PasteSpecial from writing over locked cells on a protected sheet?
4. Is there another way to lock cell notes from being copied?
5. Any other solution that I have not thought of?

I would appreciate any help as this is becoming quite frustrating.

Regards

Ben

 

[steve the fish]

If you paste into locked cells on a protected sheet you should get runtime error 1004. What code allows you to paste into locked cells on a protected sheet?

 

[sonben]

If you paste into locked cells on a protected sheet you should get runtime error 1004. What code allows you to paste into locked cells on a protected sheet?

Hi Steve the Fish,

You are right - however I think I have discovered a genuine Excel bug. In the spreadsheet I have it throws no errors. It is protected sheet with a mixture of locked and unlocked cells. Sheet is definitely protected (I have check via code before the paste). The paste command is simply Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False and it pastes to the sheet regardless if the cells are locked or not when the sheet is protected. Trying to reproduce the results in another workbook and can't do so and an error is thrown. Very very odd. For the life of me I can't work out why this behaviour would be different in this particular workbook.

I did manage to hack a very ugly solution to my problem I used an win api call GetClipboardSequenceNumber and created a custom copy event. What I do in the event is make a public variable CopyRange equal to the current selection. Then before I get to the Selection.PasteSpecial I check range Selection.Resize(1, 1).Resize(CopyRange.Rows.Count, CopyRange.Columns.Count) for any locked properties. Ugly code and not that stable. If any VBA error is thrown the event wont fire - it needs to be reset. This spreadsheet needed to be distributed yesterday, so it has gone out with this ugly fix.

Frustrating - what would have been nice would have been a Workbook_BeforeCopy(ByVal CopyObject as Variant) and Workbook_BeforePaste(ByVal DestinationObject as Variant) events. This would have made my life a lot simpler.