Unlocking specific cells (ranges) in several worksheets using VBA

Andreas Hermle
Andreas Hermle used Ask the Experts™
on
Dear Experts:

on the current workbook I would like to perform the following action running a macro:

Ranges B4:C27 in worksheets 2 to 14 have to be unlocked (Format cells - Protection - unchecking the 'Locked' Check Box).

I would like to perform this action in one go using  a macro.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
Use a macro that enumerates between your sheets and does what you want
Sub unlockcells()
Dim i As Long
Dim sh As Object

For Each sh In ActiveWorkbook.Sheets
    sh.Activate
    sh.Cells.Range("B4:C27").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
Next sh
End Sub

Open in new window

Andreas HermleTeam leader

Author

Commented:
Dear xtermie,

thank you very much for your swift help. I am afraid to tell you that your code throws an error message on line 8:

Runtime Error 1004: The 'locked'-property of the range object cannot be set (translated from Germany).

Regards, Andreas
Andreas HermleTeam leader

Author

Commented:
Dear xtermie:

ooops my fault. One of the worksheets is protected. And this throws the error message.

So I got two question:

How has the macro to be re-written to consider the following facts?

1) Only sheets 2 to 14 are to be affected by this macro
2) Any sheets that are protected need to be unprotected (password: MyPassword) on the fly and then reprotected after the macro has been run.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Most Valuable Expert 2011
Top Expert 2011
Commented:
Sub unlockcells()
Dim i As Long
Dim blnProtected as boolean
Const strPass as string = "MyPassword"

For i = 2 to 14
 with ActiveWorkbook.Sheets(i)
    blnProtected = .protectcontents 
    if blnprotected then .unprotect password:=strPass
    .Range("B4:C27").Locked = False
     if blnprotected then .protect password:=strPass
  end with
Next i
End Sub

Open in new window


for example.
Top Expert 2011

Commented:
Are the names of the sheets 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12?
The above code would work IF the numbering of the sheets is sequential.  In the case that you have moved sheets around or insert-deleted them, there could be a problem.
LMK
Andreas HermleTeam leader

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 400 points for rorya's comment #37996582
Assisted answer: 100 points for xtermie's comment #37996392
Assisted answer: 0 points for AndreasHermle's comment #37996558

for the following reason:

Dear rorya, dear xtermie: <br /><br />thank you very much for your time taken and your professional support. I really appreciate it. <br />xtermie: although you were the first to answer, rorya's answer is the best and most concise. <br /><br />Again, thank you very much for your great and quick support. <br /><br />Regards, Andreas
Top Expert 2011

Commented:
ok
Top Expert 2011

Commented:
When this is closed do we receive the points as the author states? or no points?
Most Valuable Expert 2011
Top Expert 2011

Commented:
We should get the points. Not sure why Andreas doesn't just allocate them though ;)
Top Expert 2011

Commented:
Sorry for the mixup, now I know better!
Thanks admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial