Be the first user to complete this post
|
Add to List |
Excel-VBA : Range - Lock/Unlock Specific Cells
There are two different cases you might deal with,
- Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)
- Lock the Enitre worksheet except few cells
Lock only few specific cells and rest of the cells in the work sheet are not locked
Steps:
- Lock the selected cells
- Provide the password
Note: If you wont provide the password then your cells will not be locked.
Code :
Sub sumit() Dim mainworkBook As Workbook Set mainworkBook = ActiveWorkbook mainworkBook.Sheets("Main").Range("A1:C5").Value = "Locked" mainworkBook.Sheets("Main").Range("A1:C5").Locked = True ActiveSheet.Protect Password:="xx" End Sub
![Cell Lock](/static/media/excel/2014/10/Cell-Lock.jpg)
Try editing any cell which is locked.
![Cell Lock 2](/static/media/excel/2014/10/Cell-Lock-2.jpg)
Lock the Enitre worksheet except few cells
- Firsr choose the cells which you dont want to lock
- Then lock the entire worksheet and provide the password.
Code:
Sub sumit() Dim mainworkBook As Workbook Set mainworkBook = ActiveWorkbook mainworkBook.Sheets("Main").Range("A1:C5").Value = "Free" mainworkBook.Sheets("Main").Range("A1:C5").Locked = False mainworkBook.Sheets("Main").Protect passowrd = "xx" End Sub
![unlock specific cells -1](/static/media/excel/2014/10/unlock-specific-cells-1.jpg)
Change the cells which are free, and try changing rest of the cells
![unlock specific cells -2](/static/media/excel/2014/10/unlock-specific-cells-2.jpg)
Also Read:
- VBA-Excel: Arrays – One Dimension, Static Array
- VBA-Excel: Array Functions – Split()
- VBA-Excel: Date-Time Functions – WeekDay() and WeekDayName()
- VBA-Excel: Arrays – One Dimension, Dynamic Array
- VBA-Excel: Date-Time Functions – DateDiff()