Question posted 2010 · +9 upvotes
I want to stop others from editing the cell contents in my excel sheet using VBA. Is it possible to do this?
Accepted answer +18 upvotes
You can first choose which cells you don’t want to be protected (to be user-editable) by setting the Locked status of them to False:
Worksheets("Sheet1").Range("B2:C3").Locked = False
Then, you can protect the sheet, and all the other cells will be protected. The code to do this, and still allow your VBA code to modify the cells is:
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
or
Call Worksheets("Sheet1").Protect(UserInterfaceOnly:=True)
3 code variants in this answer
- Variant 1 — 1 lines, starts with
Worksheets("Sheet1").Range("B2:C3").Locked = False - Variant 2 — 1 lines, starts with
Worksheets("Sheet1").Protect UserInterfaceOnly:=True - Variant 3 — 1 lines, starts with
Call Worksheets("Sheet1").Protect(UserInterfaceOnly:=True)
Top excel-vba Q&A (6)
- How to clear the entire array? +58 (2010)
- How to change Format of a Cell to Text using VBA +55 (2011)
- Download attachment from Outlook and Open in Excel +43 (2012)
- Can a VBA function in Excel return a range? +36 (2009)
- 2 Dimensional array from range +34 (2013)
- Hiding an Excel worksheet with VBA +33 (2009)
excel-vba solutions on this site
— top 15%.