Results 1 to 8 of 8

Thread: How To Move Transfer Or Copy Data To A Protected Sheet

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12

    How To Move Transfer Or Copy Data To A Protected Sheet

    Hi

    The workbook is in protected mode. When I go to transfer data from the Weekly Graph sheet to Score sheet there is a error.If I unprotect the Score sheet then transfer the data it works.I want to be able to transfer data from Weekly Graph sheet to Score sheet in protected mode.(Score sheet will be in protected mode)

    thanks

    Rich

    Thread - need help to alter code - Page 2
    Attached Files Attached Files

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    you can unprotect, transfer data, protect again

  3. #3
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    That is correct but I want to transfer to the SCORE sheet in protect mode as I will not be using this sheet myself
    Maybe it is not possible to transfer to a protected sheet
    Thanks

    Rich

  4. #4
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Any thoughts on if this is possible??

  5. #5
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    Hi,

    I do not think you understand what Patel said.
    In the code for data transfer you have to put a line to unprotect the sheet and at the end of the code, another line to protect the sheet .
    In this way your SCORE sheet will be protected.

  6. #6
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13
    Code:
            Sheets("Score").Unprotect "ABCD"
            Rng2 = d
            Rng2.Columns(1).NumberFormat = "m/d/yyyy"
            With Rng2.Resize(Rng2.Rows.Count + 2, Rng2.Columns.Count)
                .Rows.RowHeight = 30
            End With
            Sheets("Score").Protect "ABCD"

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    One can also do this without unprotecting and then protecting the sheet each time. There' something called UserInterfaceOnly in Excel when you protect a sheet through VBA (I haven't come across a similar feature in the Excel interface itself, and probably Microsoft has a logic also, to which I can relate to).

    So to do this, you could project all the sheets, making it True for UserInterfaceOnly, and then continue with your normal codes, without having to Unprotect and Protect each time.

    What I generally do (if this is required) is, in the workbook code module,

    Code:
    Private Sub Workbook_Open()
        
        Dim sht As Object
        
        For Each sht In ThisWorkbook.Sheets
            sht.Unprotect "PASSWORD"
            sht.Protect UserInterfaceOnly:=True, Password:="PASSWORD"
        Next sht
        
    End Sub
    Or in a code module

    Code:
    Private Sub Auto_Open()
        
        Dim sht As Object
        
        For Each sht In ThisWorkbook.Sheets
            sht.Unprotect "PASSWORD"
            sht.Protect UserInterfaceOnly:=True, Password:="PASSWORD"
        Next sht
        
    End Sub
    Now, though the sheet is protected, any manipulation of data on the protected sheet using VBA will not be hindered due to the UserInterfaceOnly:=True argument in the protection
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Member
    Join Date
    Dec 2012
    Posts
    78
    Rep Power
    12
    Excel Fox...that solved the issue...thanks

Similar Threads

  1. Replies: 4
    Last Post: 07-08-2013, 05:36 PM
  2. Replies: 14
    Last Post: 06-24-2013, 06:17 PM
  3. Replies: 6
    Last Post: 05-20-2013, 10:06 PM
  4. Replies: 2
    Last Post: 12-26-2012, 08:31 AM
  5. Move or Copy Duplicate Rows to Difference Sheet
    By Vgabond in forum Excel Help
    Replies: 3
    Last Post: 12-08-2012, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •