CODEWITHSUNDEEP
excelvba
RWashin10
RWashin10

Reputation: 1

How to "protect" Sheets and leave some columns unlocked?

I created a button and code to LOCK (or protect) the sheets in my workbook. I also created a button and code to UNLOCK (or unprotect) the sheets in my workbook.

I have columns I want the user to be able to enter info; but I do not want the whole sheet unlocked/unprotected.

Example on my Training sheet, cells K6:U3175 I'd like unprotected. Here's what I've tried:

'This is for the Lock Button on the WOOKBOOK TIPS sheet.
'Upon clicking once on the LOCK button, the scripts below re-protecting all the worksheets.

   ActiveWorkbook.Protect Password:="password"
   Sheets("1  - TRAINING").Protect "password"
   ActiveSheet.Range("1  - TRAINING").Range("K6:U3175").Locked = False

I get the error in the code to unlock the range. How can I unprotect just that range of cells?

Upvotes: 0

Views: 357

Answers (2)

DoomedJupiter
DoomedJupiter

Reputation: 73

You'll want to follow the order of events provided by Gary's answer. In code, this'll look like

With ThisWorkbook.Worksheet(1)
    .Unprotect "1234" 'insert your pasword here (1)
    .Range("A:B").Cells.Locked = False '(2)
    .Protect "1234" 'insert your pasword here (3)
End With

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

In the following order:

  1. un-protect the worksheet
  2. change the lock/unlocked setting on any cells or groups of cells
  3. re-protect the worksheet

Upvotes: 1

Related Questions