-2
Sub ProtectAll()

    Sheet4.Protect Password:="xx2016"

End Sub

Please help improve code to only lock one worksheet

Community
  • 1
  • 1
AAA
  • 7
  • 2
  • 2
    How do you need this "improved"? Is it working or not? Do you need it to do something different? Something more? Faster? Without some more info, your question is extremely unclear – psubsee2003 Sep 26 '16 at 12:09
  • 2
    This code will only lock `sheet4` so it does exactly what you're asking. If you're other sheets are also locked it's a result of something else other than this code. – SierraOscar Sep 26 '16 at 12:36
  • AAA did you meant locking one cell or worksheet? As users above mentioned what you posted does exactly what you ask for, can you please provide some more details about the problem and any other code? If any other worksheet is locked it probably was either already locked before or you have another sub that locks all. – Pav Sep 27 '16 at 08:15

1 Answers1

1

It would be great if you could provide more information about your actual problem. I guess, that you want to lock a specific cell but if you lock the sheet, all cells are locked. If this is so, here is the solution. If not, please explain your problem!

First of all you need to change the "Locked" attribute for all cells to false (default is true). Then, you selt the "Locked" attribute to true for all required cells. Then, you can lock the sheet.

Sub Makro2()
    Cells.Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Range("E6").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Range("E4").Select
End Sub

Edit 1: Thanks to Pav, here is a better solution that avoids "selects" but is working in the same way:

Sub Makro2()
    Cells.Locked = False
    Cells.FormulaHidden = False
    Range("E6").Locked = True
    Range("E6").FormulaHidden = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="xx2016"
End Sub
Stefan
  • 1,253
  • 2
  • 12
  • 36
  • 1
    Hi Stefan, I assume that is recorded macro, please have a look at [avoid select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – Pav Sep 27 '16 at 08:31
  • Hi Pav. That is true, it was just recorded because I did not know the correct syntax but I thought, that this was the problem. But you are correct, select should be avoided, therefore I will add a better solution. Thanks! – Stefan Sep 27 '16 at 16:45