Cells unlocked after reopening!

[Ste_Moore01]

Hi,

I've locked a lot of cells in my spreadsheet and changed it so that I can only select unlocked cells.

The problem is, when I saved the file and reopened it I could select every cell rather than just the ones that were unlocked.

Does anyone know why this is and is there a solution?

 

[RAM]

Possibly you have protected the sheet and selected both Select locked cells and Select unlocked cells in "Allow all users of this worksheet to:"

If this was the case unselect Select locked cells and OK.

If you want it like this every time you open the workbook whether you close it protected or unprotected paste this code in the workbook code

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
.EnableSelection = xlUnlockedCells
.Protect Contents:=True, UserInterfaceOnly:=True
End With
Next ws
'select statsgrid as the first sheet you see when you open the wb
    Sheets("Sheet1").Activate
'select cell E11 on Stats Grid
    Sheets("Sheet1").Range("E11").Select
End Sub

Change Sheet1 to your worksheet name and change the range to fit your book.

HTH

RAM

 

[Ste_Moore01]

Thank you very much RAM.

This works perfectly.

I'm still confused as to why it did this though.

When I locked the cells I used a macro

Sub Protect()
    Windows("pw.xls").Visible = True
    Sheets("PasswordSheet").Select
    Dim Password As Range
    Set Password = Range("B1")
    ActiveWindow.Visible = False
    ActiveSheet.Protect Password
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

So it should have locked the cells without being able to select the locked cells.

 

[Ste_Moore01]

I've come across a small problem when using your code.

When I use it, it protects all of my worksheets when I only want it to protect one of them.

Is there any way to modify this code so that happens?

 

[Ste_Moore01]

Any suggestions?

 

[RAM]

First of all to answer you questions I don’t really write and read code, but I will do my best for you.

[Edit] I tried your code and it didn't protect my worksheet at all. It isn't a workbook opening code. It's a code that must be run through a button or Alt+F8. That code is not run when you open the workbook.
[Edit 2] I then tried to run it from the worksheet, but I got a error. Didn't you? Here's some[simple] codes I've recorded to protect and unprotect my sheets using codes. Note the keyboard shortcuts.

Sub Unprotect()
ActiveSheet.Unprotect
' Keyboard Shortcut: Ctrl+e
    ActiveSheet.Unprotect    
End Sub
Sub Protect()

ActiveSheet.Unprotect
' Keyboard Shortcut: Ctrl+r
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells    
ActiveSheet.Protect       
End Sub

[Edit 3] Open up a new module and place the codes there.

You will easily know if the sheet is protected if you place the Protection toolbar in the menu: View – Toolbars – and click on Protection. Then drag and drop that toolbar in the menu.

Good that my code I got from this board worked for you and in order to correct it for your needs try this.

Private Sub Workbook_Open()

    Sheets("Sheet1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    'select statsgrid as the first sheet you see when you open the wb
    Sheets("Sheet1").Activate
    'select cell E11 on Stats Grid
    Sheets("Sheet1").Range("E11").Select
End Sub

[Edit 4] Right-click on the Excel symbol in the top left corner and choose View Code and paste the code in there.

It's not the best looking code, but it appears to work. I will be glad to see a better code suggested by some more VB savvy member.

 

[Ste_Moore01]

The macro I used to lock the sheets isn't a macro that is automatically run when I open the workbook, it uses Ctrl+P. It looks up the password to lock the sheets from another workbook that opens with it.

So essentially it is saying

Sub Protect() 
    ActiveSheet.Protect "Password"
    ActiveSheet.EnableSelection = xlUnlockedCells 
End Sub

and it would use the word Password as a password.

It works fine when I use it but the cells aren't locked when reopening the file.

I'm not currently at work so I'll try your code tomorrow.

Thanks for your reply!

 

[Ste_Moore01]

I've now tested your code and it's still reopening the workbook with selectable locked cells.

 

[RAM]

Sorry

Private Sub Workbook_Open()

    Sheets("Sheet1").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
    'select statsgrid as the first sheet you see when you open the wb
    Sheets("Sheet1").Activate
    'select cell E11 on Stats Grid
    Sheets("Sheet1").Range("E11").Select
End Sub