Excel clears clipboard on protecting/unprotecting sheet by VBA but retains it when done by using UI i.e Review -> Protect / Unprotect?

1
votes

I have come across a strange problem in Microsoft Excel for Mac 16.11. It is equally true for Windows version too. If I copy some cell(s) and use VBA to protect or unprotect the sheet, the clipboard clears. Here is the code that I use for protect - unprotect :

Sheet1.Unprotect("abc")
Sheet1.Protect("abc")

But if I copy some cell(s) and use the UI i.e Review -> Protect, Unprotect, the clipboard is retained and I can even see that the dotted green coloured border across the copied cells is intact, unlike the former case.

I can use MSForms.DataObject to manually save the clipboard text(it works) before calling protect / unprotect and restore it after the call but then the dotted green coloured border goes away which can cause confusion to user.

Is there a way to imitate in VBA what the UI does when protecting /unprotecting ?

1
vbaexcel
Use worksheet.protect method with the UserInterfaceOnly=true once and you will never have to unprotect it again to perform vba actions. – user4039065
I had tried it many times. Sadly there are two major problems. I am using DrawingObjects:=False, UserInterfaceOnly:=True, AllowFormattingCells:=True. First something like TargetRange.ClearFormats on a protected sheet causes excel to crash. Secondly code to add drop down menu to cell doesn't work on a protected cell. – Gaurav Sharma
Can you just copy the selection again after protecting (Sheet1.Protect: Selection.Copy) or unprotecting: Sheet1.Unprotect: Selection.Copy? – paul bica

1 Answers

3
votes

I came across something while researching something else, I think it might be an answer to this, see http://www.excel-first.com/autoexpand-excel-tables-on-protected-sheets/

In short, it seems it's a common problem when running macros/code and that you can prevent Excel from clearing the clipboard by opening it first (you then have to close it after) using:

OpenClipboard 0

~the action that would make Excel clear the clipboard~

CloseClipboard