Protect/Unprotect Sheet

[Noz2k]

I've managed to protect a worksheet, and prevent the user from typing in the cells. However how can I make it so that the user cannot select the cells?

Also when I click to unprotect the sheet it prompts me for a password and then doesn't seem to recognise the password

here is my code

Dim ws As Excel.Worksheet
Set ws = Sheets("MainMenu")
ws.Protect Password:="abc123", UserInterfaceOnly:=True
ws.Range("A1:L30").Locked = True
ws.Unprotect Password:="abc123"

 

[ogo]

If the password doesn't work check which sheet is selected.Below code worked fine for me

Sub Protect()
'
Dim ws As Excel.Worksheet
Set ws = Sheets("MainMenu")
ws.Range("A1:L30").Locked = True
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=False, Password:="abc123", UserInterfaceOnly:=True
ws.EnableSelection = xlUnlockedCells

End Sub

Thanks,

Ogo

 

[Noz2k]

Thanks,

doesn't seem to make any difference though. Can still select the cells, and still not recognising the password when I click to unprotect.

It gives me runtime error 1004

If I remove the ws.unprotect, then the protection works fine (doesn't allow me to select cells). But I still can't unprotect the sheet

 

[Noz2k]

Any idea?

I tried creating a seperate sub

Public Sub Unprotect_The_Sheet()
ActiveWorkbook.Worksheets("MainMenu").UnProtect Password:="abc123"
End Sub

but it just gives me this error message

"Run-time error '1004':
 
Application-defined or object-defined error"

Any clue?

 

[GTO]

Firstly, use ThisWorkbook instead of ActiveWorkbook if the code is in the workbook you want to run against.

I would then check "MainMenu" by selecting the tab and see if the tab name actually has an errant leading/following space(s)

 

[Noz2k]

The sheet is definitely named '"MainMenu" it works for the .protect code

Tried ThisWorkbook and still generate the same error

Have also tried

Dim ws As Excel.Worksheet
Set ws = Sheets("MainMenu")
ws.Unprotect Password :="abc123"

but when I try and run the code I still get the same error.

Everything I read seems to say that this should work, but when I go to review-changes-unprotect sheet it prompts for a password but doesn't recognise it.

 

[Noz2k]

Ok so I just took a shot in the dark and typed "password" for the unprotect password and it worked.

Have just searched through my whole project (using the find and replace feature), and not anywhere is "password" related to unprotect.

As an aside my code is now working, and the password has changed to what I want it to be.