Code for locking all cells

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
Is there some code to protect and unprotect a sheet were it will lock all the cells? I currently have this.

ActiveSheet.Unprotect

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
After the sheet is unprotected, you need to make sure all the cells are locked:

activesheet.usedrange.locked = true

before you re-apply the protection
 

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
ok. I should have showed you the whole Macro. My objective is to have the Macro do that for me. I have cells that can be edited, but when I run this macro I would like for it to lock all the cells for me.. Is that possible?

Sub FINAL_BURNING_MACHINES()
'
' final Macro
'
'
ActiveSheet.Unprotect
Range("J24:J26").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = 1
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "FINAL"
Range("A1:T23").Select
ActiveSheet.PrintOut

Range("A1:T23").Select
ActiveWorkbook.Save
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True



ActiveWorkbook.Save
End Sub
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
If you lock the cells then users won't be able to edit them - is that what you wanted?

If so, cleaned up code could be:

Code:
Sub FINAL_BURNING_MACHINES()
'
' final Macro
'
'
With ActiveSheet
  .Unprotect
  With .Range("J24:J26").Interior
    .Pattern = xlSolid
    .PatternColorIndex = 1
    .Color = 255
    .TintAndShade = 0
    .PatternTintAndShade = 0
  End With
  .Range("J24").FormulaR1C1 = "FINAL"
  .PrintOut
  .UsedRange.Locked = True
  .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
ActiveWorkbook.Save
End Sub
 

bamaisgreat

Well-known Member
Joined
Jan 23, 2012
Messages
794
yes, when I run the Macro I want the entire sheet locked were it is not able to be modified. Thanks
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
In the code above you would just need to add a Password argument:

Rich (BB code):
.Protect Password:="SomePassword"DrawingObjects:=True, Contents:=True, Scenarios:=True
 

Watch MrExcel Video

Forum statistics

Threads
1,095,176
Messages
5,442,835
Members
405,201
Latest member
kashyap44

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top