Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

Excel-VBA : Range — Lock/Unlock Specific Cells

There are two dif­fer­ent cases you might deal with,

  • Lock only few spe­cific cells and rest of the cells in the work sheet are not locked(means you can edit them)
  • Lock the Eni­tre work­sheet except few cells

Lock only few spe­cific cells and rest of the cells in the work sheet are not locked

Steps:

  • Lock the selected cells
  • Pro­vide the password

Note: If you wont pro­vide the pass­word 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 edit­ing any cell which is locked.

Cell Lock 2

Cell Lock 2

Lock the Eni­tre work­sheet except few cells

  • Firsr choose the cells which you dont want to lock
  • Then lock the entire work­sheet and pro­vide 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 spe­cific cells –1

Change the cells which are free, and try chang­ing rest of the cells

unlock specific cells -2

unlock spe­cific cells –2

 

You may also like...

1 Response

  1. April 8, 2015

    […] You will need to unpro­tect the range before you can update it. Smitty’s post show show you can protect/unprotect ranges via VBA code. Here is a link to some code that shows you to protect/unprotect in VBA when pass­words are involved: Excel-VBA : Range — Lock/Unlock Spe­cific Cells […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: