Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Unlocking specific cells (ranges) in several worksheets using VBA

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
SOLUTION
Avatar of Anastasia D. Gavanas
Anastasia D. Gavanas
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreas Hermle

ASKER

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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
When this is closed do we receive the points as the author states? or no points?
We should get the points. Not sure why Andreas doesn't just allocate them though ;)
Sorry for the mixup, now I know better!
Thanks admin