Protect UserInterfaceOnly:=True

[wschiro]

Hi

I would like to protect a worksheet and at the same time be able to do calculation, cut and paste functions via macros.

This is the code I am using

Private Sub Workbook_Open()
Sheets("Calculations").Protect UserInterfaceOnly:=True
end sub

This code protects the sheet but all I have to do is tools>protection> unprotect sheet from the menu and this unprotect the sheet. I do not need to supply a password.

Is there a way to use a password with the

sheet1.Protect UserInterfaceOnly:=True

Charles

 

[DRJ]

Sheet1.Protect Password:="Password", UserInterfaceOnly:=True

 

[wschiro]

Thanks DRJ,

I was missing the comma so I was getting a syntax error.
Then I tried using two line of code and that resulted in the original post scenario.


Sheets("Calculations").Protect Password:="Password"
Sheets("Calculations").Protect UserInterfaceOnly:=True

Thanks for the fast responds. To further my understanding why doesn’t the above code work? Well, works as far as protects worksheet but no password


Charles

 

[DRJ]

That code works for me.

You don't need to protect the sheet with a password just to use the user interface option. If the sheet already is protected with a password just use the userinterfaceonly line. For example you can do this when the workbook opens.

Private Sub Workbook_Open()

Dim Sh          As Worksheet

    For Each Sh In Worksheets
        Sh.Protect UserInterFaceOnly:=True
    Next
    
End Sub

Then any macro can make changes and you don't have to worry about unprotecting the sheets.