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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,287
Office Version
  1. 365
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

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
Top