CODEWITHSUNDEEP
excelvbaprotected
dawm89
dawm89

Reputation: 1

Protect/unprotect sheet

I built a small Excel tool that asks the user to input cost elements and spits out an output.

I created Lock and Unlock buttons that allow the admin user to protect and unprotect all sheets for editing purposes. I have a total of 10 sheets in the tool. The buttons work except for two sheets.

The Unlock macro unprotects all sheets except the Cost Inputs sheet.
However if I use the small piece of the code that unprotects the Cost Inputs sheet in the immediate window, it works.

Sub admin_unlock_Click()
  Application.ScreenUpdating = False
  Sheets("Home").Unprotect Password:="xxx"
  Sheets("Cover Page").Unprotect Password:="xxx"
  Sheets("Study Categorization").Unprotect Password:="xxx"
  Sheets("Cost Inputs").Unprotect Password:="xxx"
  Sheets("Price Indicators").Unprotect Password:="xxx"
  Sheets("Benchmarking Output").Unprotect Password:="xxx"
  Sheets("Output Sheet").Unprotect Password:="xxx"
  Sheets("Instructions").Unprotect Password:="xxx"
  Sheets("Glossary").Unprotect Password:="xxx"
  Sheets("Export to CRM").Unprotect Password:="xxx"
  Application.ScreenUpdating = True
End Sub

The Lock macro protects all sheets except the Output Sheet.
It also does not work if I place the relevant bit of code in the immediate window.

Sub admin_lock_Click()
Application.ScreenUpdating = False
Sheets("Home").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Cover Page").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Study Categorization").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Cost Inputs").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Price Indicators").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Benchmarking Output").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Output Sheet").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Instructions").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Glossary").Protect Password:="xxx", UserInterFaceOnly:=True
Sheets("Export to CRM").Protect Password:="xxx", UserInterFaceOnly:=True
Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 5357

Answers (3)

Dave Green
Dave Green

Reputation: 11

Be aware that Excel has an issue with .Unprotect !

Using the assigned object to perform unprotect is unreliable, such as:

ws.Unprotect

This method may fail and can be a problem to resolve if not aware of the issue.

To be sure, unprotect the sheet directly (by name or number)

Sheets("MySheet").Unprotect

or

Sheets(3).Unprotect

Hope this helps DG

Upvotes: 1

urdearboy
urdearboy

Reputation: 14590

Consider a sheet loop rather typing out each sheets name like so ~

Sub admin_unlock_Click()

Dim ws As Worksheet

For Each ws In Worksheets
    ws.Unprotect "xxx"
Next ws

End Sub


Sub admin_lock_Click()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    ws.Protect "xxx", UserInterFaceOnly:=True
Next ws

End Sub

If you are locking every sheet, why not just lock the book instead? Then you only have to protect/unprotect the book rather every single sheet.

Upvotes: 1

Vityata
Vityata

Reputation: 43595

The .Protect protects the locked cells in VBA by default. Probably your worksheet does not have locked cells. As a workaround try this:

Create a new module in VBA. In the new module copy and paste this:

Sub TestMe()
    Worksheets("Cost Inputs").Cells.Locked = True
End Sub

In the immediate window, write TestMe and press Enter. Run your code again. Is it protected? If it is not, then you probably have On Error Resume Next on your code.

Upvotes: 0

Related Questions