Does UserInterfaceOnly:=True work on closed wb when pasting data to it?

When copying data from a workbook and pasting it to a closed workbook, does "UserInterfaceOnly:=True" work, or do you have to unlock it then relock the workbook to allow pasting to it? Thanks.

Originally Posted by alansidman

When you say "unlock" and "relock" are you referring to a password protected workbook. I cut and paste monthly a bank statement I receive to a closed workbook and do not unlock it nor use the UserInterfaceOnly functionality.

Yes, but it will not allow me to paste because the workbook is locked until I unlock the cells. In a workbook with code, to change values in locked cells, if you use the UserInterfaceOnly this allows that to happen with code, but doesn't seem to work when pasting to another workbook.

Originally Posted by thecdnmole

When copying data from a workbook and pasting it to a closed workbook, does "UserInterfaceOnly:=True" work, or do you have to unlock it then relock the workbook to allow pasting to it?

It's a bit of a mess here. UserInterfaceOnly is used to protect SHEETS, not BOOKBOOKS. Also, you can't paste a data range into a closed workbook, you need to open it first. Is special protection (UserInterfaceOnly) applied to the closed workbook, or to the workbook from which you will be copying data?
Assuming that the protection in a special mode applies to a workbook that we will open to paste into it, remember that this mode is volatile. It is important how you open the workbook where you want to paste the data. Do you allow him to run macros or not.

Please calmly and precisely describe where the special protection is applied (in both workbooks, or only in the target workbook), how you open the target workbook and when the special protection is applied.

Artik

describe where the special protection is applied (in both workbooks, or only in the target workbook), how you open the target workbook and when the special protection is applied.

When opening both workbooks, "UserInterfaceOnly" is applied, but it doesn't allow code to paste to target wb, so I have a line of code to unprotect ws and then protect it once data is pasted. Code opens the target workbook, Set wbData = Workbooks.Open("C:\Users\USER\OneDrive\Documents\Reports.xlsx").

The Reports.xlsx file has no code, so it cannot have special protection on the sheets. Unless it's a mistake and it should be .xlsm. And one more question if the opened file contains macros. Do you turn off the reaction to events of the following type:

Please Login or Register  to view this content.

by running the code for copying the range?

Artik

Sorry, should be xlsm, and yes, I turn off events.

I see my problem, "Application.EnableEvents"! Use it all the time, but was the issue in this case! Thanks.

But your question was different at the beginning. "When the sheet is in UserUnterfaceOnly mode, can the data be pasted into it using the Copy-Paste method"?
Answer: No, you can't. You need to unlock the sheet. But...
You can transfer the data with the method Range.Value = Range.Value. Only values, no formatting. For example:

Please Login or Register  to view this content.

I assumed that EnableEvents = True and in the Workbook_Open event (in Target book.xlsm) the target sheet is protected in UserInterfaceOnly mode.

Artik

I see, I would prefer to use your code Artik rather than copy and paste. How do I get it to paste in the next available row in the target ws?

On the forum you will find many examples of calculating the first free row or the last non-empty cell (and the first free row is the last non-empty +1 )

Artik