Avatar of Andreas Hermle
Andreas Hermle
Flag 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
Microsoft Excel

Avatar of undefined
Last Comment
Anastasia D. Gavanas

8/22/2022 - Mon
SOLUTION
Anastasia D. Gavanas

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Andreas Hermle

ASKER
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
Rory Archibald

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Anastasia D. Gavanas

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Andreas Hermle

ASKER
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
Anastasia D. Gavanas

ok
Anastasia D. Gavanas

When this is closed do we receive the points as the author states? or no points?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rory Archibald

We should get the points. Not sure why Andreas doesn't just allocate them though ;)
Anastasia D. Gavanas

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