Macro to protect a workbook with password protected sheets

[mr_samd]

Hi,

I have a workbook that acts as a sort of questionaire. Each day, employees fill in the data for the day (batch numbers, dates, times of work, ect) Most cells are locked and protected apart from the answer cells. This is all on 3 sheets (all protected).

I want to run a macro that can be used at the end of every day, that locks all the answers so they can not be changed at a later date. I have tried "starting recording macro, unprotect the sheet, lock all cells, protect the sheet" and repeat for all three sheets. This works apart form needing to enter the password for every sheet to unprotect it.

Is there a way to do this so the employees themselves can lock it, without needing the password (that only the manager has)?

Any help would be appreciated.

Sam

 

[Misca]

Try something like:

Sub ProtectSheets()

Dim i As Integer


On Error Resume Next


For i = 1 To 3
    With Sheets(Choose(i, "Sheet1", "Sheet2", "Sheet3"))
    
        .Activate
        .Unprotect ("Password")
        
        With Selection.SpecialCells(xlCellTypeConstants, 23)
            .Locked = True
            .FormulaHidden = True
        End With
        
        .Protect ("Password")
    
    End With
Next i


End Sub

Before running the code for the first time you should unlock the cells on each worksheet you want to protect.

The macro activates the sheet, unprotects it, locks the cells with constants (=value entered into it) and protects the sheet at the end. If your sheets have a certain pattern (as they should) you might want to change the logic how the macro knows what are the cells that need to be locked. The locked cells are the ones you can't change once the sheet is protected.

 

[mr_samd]

Ok thanks! This has helped. i tweaked it a bit but i didnt know you could programme the password into the VBA. All working now! Cheers!