Excel paste data from external source while worksheet is password protected

Question

I'm setting up a excel worksheet. In this sheet, the user can only interact with cells in a certain range, eg. ("A1:B10"). So I set those cells to be "unlocked" cells, and password protect the entire sheet, with the option to only allow the user to "select unlocked cells".
One thing I'm facing is, while the worksheet is password protected, it needs to have the ability to allow the user paste data from external sources. How can I enable the paste function while the worksheet remains password protected?

UPDATE:
This is in a macro-enabled workbook.
I used the following code to enable and disable some functions in the workbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.Calculation = xlAutomatic
End Sub

Private Sub Workbook_Open()
    Application.Calculation = xlAutomatic
    Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.Calculation = xlAutomatic
    Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Application.Calculation = xlAutomatic
End Sub

I found that, when I take the code out, save and re-open the workbook, pasting is working OK. Once I paste the code in, save and re-open the workbook, pasting doesn't work.

I don't get the issue, if those cells are unlocked and the user can select them, then pasting with a protected sheet into said unlocked cells is already allowed. — Tim Wilkinson
@TimWilkinson I tried to copy some data from another workbook, the pasting doesn't work. — f_qi
That entirely depends on how the worksheet was protected and whether that ability was blocked or not. If you try it and it bombs because of the protection, then you can't. If you try it and it works without un-protecting it, then you can. Can you not unprotect the sheet, do the paste, and re-protect it? — Mathieu Guindon
In that case the answer to your question is "you can't". Either unprotect more cells, or run a macro on worksheet activate to check the size of the copied range, if it is too large issue a message box informing the user before hand to copy a smaller range. — Tim Wilkinson

f_qi f_qi · Accepted Answer · 2017-02-23T19:56:03

After a few hours of testing, I finally solved it by removing the code in Workbook_WindowActivate section. Somehow this prevent the paste function to work probably.

Excel paste data from external source while worksheet is password protected

1 Answers