Protect / Unprotect Worksheets

    I was able to use the "Excel: Password Protect/Unprotect All Excel Worksheets in One Go." page to set up this function and it works great. However, I also need it to hide the contents of the cells on the protected sheets. Is this possible and if so how do I get it done? Thanks.

    Re: Protect / Unprotect Worksheets


    You can just hide the row that contains the cell in question. You can also move the data that you want hidden to another worksheet and then hide this work sheet.


    CJ

    Re: Protect / Unprotect Worksheets


    I am sorry, I wasn't very clear in my original post. I know that I can hide a selected group of cells, rows or columns. I want to allow someone to see the information, but not be able to select any cells on the page. For instance, if they try to click on the cell, I want nothing to show up in the formula bar. If you go in to protect each individual sheet (Excel 2002) it gives you the option to allow users multiple rights. The default that comes up is to allow users to "Select Locked Cells" and "Select Unlocked Cells". Is there a way to write into the macro to not allow users to do anything with protected or locked cells (i.e. select, edit, write, etc.)?

    Re: Protect / Unprotect Worksheets


    To not allow any cells on a sheet to be selected add this to your protect/unprotect code:

    Code
    ActiveSheet.EnableSelection = xlNoSelection


    and this to the sheet object to suppress double click error messages:

    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
End Sub



    To allow selection of unprotected cells only

    Code
    ActiveSheet.EnableSelection = xlUnlockedCells

    Re: Protect / Unprotect Worksheets


    Thank you for your help. The recommendations worked onthe pc where we created the macro, but when we saved and emailed the spreadsheet it was protected but it still allowed selection of cells. Here is the code that we used.
    [VBA]
    Private Sub CommandButton1_Click()
    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
    wSheet.EnableSelection = xlNoSelection
    wSheet.Protect Password:=TextBox1.Text
    Next wSheet
    Unload Me
    End Sub
    [/VBA]
    Could you tell me what I might need to change/add to make this work? Thanks again for taking the time to help out a rookie.

    Re: Protect / Unprotect Worksheets


    Even if the xlUnlocked cells is done, it is undone when you close the workbook (one of Excels wonderful quirks).


    You must run that code in the Workbook_Open event, in the workbook object. Then that is done when the workbook is opened.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login