How To Move Transfer Or Copy Data To A Protected Sheet

[rich_cirillo]

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

[patel]

you can unprotect, transfer data, protect again

[rich_cirillo]

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

[rich_cirillo]

Any thoughts on if this is possible??

[Ingolf]

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.

[bakerman]

        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"

[Excel Fox]

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,

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

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

[rich_cirillo]

Excel Fox...that solved the issue...thanks