The Paste Range address

sonben

New Member
Joined
Dec 6, 2012
Messages
3
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,517
Messages
6,131,116
Members
449,622
Latest member
lejohnson93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top