Be the first user to complete this post

  • 0
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
Cell Lock

Try editing any cell which is locked.

Cell Lock 2
Cell Lock 2

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
unlock specific cells -1

Change the cells which are free, and try changing rest of the cells

unlock specific cells -2
unlock specific cells -2



Also Read:

  1. VBA-Excel: String Functions – Replace()
  2. VBA-Excel: Arrays – One Dimension, Static Array
  3. VBA-Excel: String Functions – LTrim()
  4. VBA Excel - Cells, Ranges and Offset: Refer to Cells by Using Shortcut Notation