Andreas Hermle
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
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
ok
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
Thanks admin
ASKER
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