copy into protected sheet

[lbass]

I have 4 worksheets in a workbook.
Each is password protected with select unlocked cells being the only option checked.
On the first sheet I can paste data from another workbook.
On the other three I can not.

Any idea what could be different to allow this? Or what I can do to allow pasting into the other three sheets? I can't remember if I did anything different or not. Getting old.

Yes I looked in help. Yes I did a search. No I'm not smart enough for that to have done any good.

Thanks

 

[bk]

Not sure there's enough info to be sure

but questions to answer might be:

1- Are the cells you're pasting to on the first sheet locked or unlocked (I feel sure they're unlocked if the sheet is protected).

2- Are the cells you're attempting to paste to on the other 3 sheets locked or unlocked (I suspect locked)?

After this, you can decide how you want to proceed. You can paste to unlocked cells on the other 3 sheets, or unlock the cells you're trying to paste to.

Or we could go the route of writing some code to protect and unprotect the sheets before/after you do your pasting? But it would depend on the context of what/when you're copying.

Maybe this is a start for ya, at least.

 

[lbass]

Thanks for the reply.

The only cells the cursor will enter are unlocked on all sheets.

I had rather not use code. I send the sheets to others to enter the data. Some use their own sheet and want to just copy into mine.

Mine is full of checks through formula and I don't want to have to check them to insure they haven't been changed.

I may be wrong but believe if my password is still valid then it hasn't been broken into???

 

[bk]

Well,

since you are certain that you're actually trying to paste to unlocked cells on a protected sheet,

-What is the size of the range that you're copying (ex. 4 cells wide by 3 cells tall) and on the protected sheet you're trying to paste to, is the adjacent range (downward and rightward from the cell you select) at least as large? In other words, is every single, solitary cell you're trying to paste to unlocked for absolute certain?

-What type of error/message/what-happens when you try to paste to these other 3 sheets?

 

[lucky12341]

Sorry if I am a bit excited this might be the first question I can answer hopefully (leanring VBA still)...

A nice bit of code I use is VBA but it does a lovely as heck feature that prevents an issue similar to yours that I had. Instead of protecting the worksheets as excel normally does that causes copy and paste issues use this small amount of VBA code. Place it under the workbook itself not the sheets and it will auto protect whatever sheets you have protected cells in but doesnt hinder copy and paste feature...(I hope it works)
Also just copy and paste it under the same sub for each sheet you want to protect...the whole code is needed to work effectively I have found...plus you get to use autofilter if you want

Private Sub Workbook_Open()
    Sheet1.Protect Password:="admin", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    Sheet1.EnableAutoFilter = False
End Sub

Example of wanting 2 sheets protected

Private Sub Workbook_Open()
    Sheet1.Protect Password:="admin", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    Sheet1.EnableAutoFilter = False
    Sheet2.Protect Password:="admin", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    Sheet2.EnableAutoFilter = False
End Sub

Maybe a better way but I hope it helps...

 

[lbass]

bk Thanks again. I am 98% sure all the cells required are unlocked.
The error message is:
"The cell or chart you are trying to change is protected and therefore read-only.
To modify a protected cell or chart, first remove protection using the Unprotect sheet command. (Tools menu, Protection sub menu, Protection submenu). You may be prompted for a password.”

lucky12341 Thanks, I had wanted not to use code since I am sending this to others. But I am willing to try your code. I am very weak in VBA and don't understand "under the workbook itself not the sheets"?
Since my sheets are named should I use their name instead of sheet 1? I have 4 sheets so I should use your second example expanded for two more sheets? The way I had them protected the user could only access the unlocked cells.

 

[lucky12341]

lbass,
Go tools>macro>visual basic editor and double click the work book and copy and paste the code. You have to use the sheet numbers themselves not the names and the numbers should be in paranthesises making them an easy find. VBA isnt always the best options but this worked great for me when I had similar issues.

The code will allow the user to select any cell but only change unprotected cells.

 

[bk]

What was the size of the range?

The message you're receiving sounds like the classic message one gets when trying to enter into locked cells on a protected sheet. You said you were 98% sure?

You might select the entire range that you're pasting to (where you get the error) and the, go to Format\Cells on the 'Protection' tab and see if the checkbox for Locked is Checked, Unchecked or "Grayed". If it is unchecked, then you can raise your 98% certainty to 100%. But if it is gray then, you have at least one cell in there that is the cause of your trouble. If you do have a grayed or checked checkbox, all you have to do is totally check the box and then, uncheck it and it will unlock all the selected cells.

Repeat this for the other sheets.

Let me know if this works or not. But I'll be surprised if it doesn't.

 

[lbass]

BK
2% will get you everytime. That did it.

Thanks