Copy paste with vba protection on

Hi Guys, I have a worksheet with the protection code below

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True

It will not allow me to copy paste on the sheet.

What if anything can be added to the code to allow copy paste in the unlocked cells.

Thank you,
Robret
rsen1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
You might get some ideas here.
0
dlmilleCommented:
Copy/Paste works fine in the unlocked cells.  Are you sure you're testing your paste in unlocked cells?  unlock the sheet, select the cells and right click then FORMAT->Protection and make sure the Locked checkbox is NOT checked.

See attached, the yellow shaded cells are unlocked.  On workbook open, Sheet1 is protected with your command.

Try copying from cell B4 and paste to another yellow range.  Works, correct?

You can also copy from protected area to this yellow range as well.  Works, correct?

Dave
copyPasteProtected-r1.xls
0
rsen1Author Commented:
Dave, Thank  you for your response, please see the attached sample from my workbook.

Robert
protect.xlsm
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

dlmilleCommented:
You're not being allowed to copy/paste because of your worksheet_selectionChange() event.  It is setting protection on every change and as a result, the clipboard is being cleared.

Any reason you're not setting protection on workbook open or close as perhaps a better alternative?

Dave
0
rsen1Author Commented:
Dave could you please send the correct code for that

Thank you
0
dlmilleCommented:
do you want to do it for all sheets on open?

Dave
0
rsen1Author Commented:
all but 2 sheets
0
dlmilleCommented:
ok - what sheets?  Also, perhaps workbook close is more appropriate as thats when the "owner" of the changes ensures everything is locked down.  No need to do it on open if the sheets are already protected.

Dave
0
rsen1Author Commented:
1-20 Labels
Blank Labels
0
rsen1Author Commented:
Thank you very, very much
0
dlmilleCommented:
Here's your code.  Note the constant at the top of the code.  Just include more sheet names to exclude, separated by commas.  The code iterates through and determines the sheet can or can't be excluded, then does the protect operation, accordingly.

Const excludeSheets = "1-20 Labels,Blank Labels" '<- put the sheets to exclude/not protect, here

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wkb As Workbook
Dim wks As Worksheet
Dim strExcludeSheets As String
Dim vExcludeSheets As Variant
Dim myDict As Object 'Dictionary holds unique names

    vExcludeSheets = Split(excludeSheets, ",")
    
    Set myDict = CreateObject("Scripting.Dictionary")
    For i = LBound(vExcludeSheets) To UBound(vExcludeSheets)
        myDict.Add vExcludeSheets(i), Nothing 'sheet names are unique so no need to test for existance
    Next i
    
    Set wkb = ThisWorkbook
    For Each wks In wkb.Worksheets
        If Not myDict.exists(wks.Name) Then 'didn't find it, so protect the sheet
            wks.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowFormattingCells:=True
        End If
    Next wks

    myDict.RemoveAll
    Set myDict = Nothing
End Sub

Open in new window


See attached.

Dave
protect-r1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rsen1Author Commented:
I don't see your post so that I can accept it
0
dlmilleCommented:
Sorry - that code goes in the ThisWorkbook codepage, not the sheet's codepage.  Just copy/paste it in.

Let me know if it works alright for you!

Hope this helps!

Dave
0
rsen1Author Commented:
Dave thank you that code works great.

Should I repost, I also have some pages with password protect and EnableAutoFilter = True
0
dlmilleCommented:
Sure
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.