Solved

Copy paste with vba protection on

Posted on 2012-03-10
15
388 Views
Last Modified: 2012-03-11
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
0
Comment
Question by:rsen1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 37705853
You might get some ideas here.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37707654
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
 

Author Comment

by:rsen1
ID: 37708214
Dave, Thank  you for your response, please see the attached sample from my workbook.

Robert
protect.xlsm
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 42

Expert Comment

by:dlmille
ID: 37708217
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
 

Author Comment

by:rsen1
ID: 37708223
Dave could you please send the correct code for that

Thank you
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37708225
do you want to do it for all sheets on open?

Dave
0
 

Author Comment

by:rsen1
ID: 37708227
all but 2 sheets
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37708229
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
 

Author Comment

by:rsen1
ID: 37708234
1-20 Labels
Blank Labels
0
 

Author Comment

by:rsen1
ID: 37708251
Thank you very, very much
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37708255
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
 

Author Comment

by:rsen1
ID: 37708256
I don't see your post so that I can accept it
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37708261
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
 

Author Comment

by:rsen1
ID: 37708272
Dave thank you that code works great.

Should I repost, I also have some pages with password protect and EnableAutoFilter = True
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37708274
Sure
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question