Apply CF Macro to Locked Worksheet

I have a Workbook that contains sheets that are all linked from live workbooks. The worksheets are Protected so that users only have a Read-Only view of the live data.

I have a macro that re-applies the CF, but it errors on the
"If blnClear Then
.FormatConditions.Delete"
section of the code.

Is there any way of unlocking the worksheet when the Macro is called, and locking it again when it finishes?

Please Login or Register  to view this content.

Amended code to the following, and it appears to have resolved

Please Login or Register  to view this content.

Hi there,

I can't test this properly without access to your workbook, but you could try the following code:

Please Login or Register  to view this content.

The highlighted value may be changed to suit your own requirements.

Hope this helps.

Regards,

Greg M

Hi Greg

Thanks for the reply. i will post up my workbook just so you can test (if you're bored)

Does it matter where the "Sheet3.Unprotect" or ".unprotect" commands go?



Can I assume that because my code starts "With Sheets("Diary_Events").Range("C4:O999")", and "Diary_Events" is Sheet3, then technically I do not need Sheet3 before my .Protect?

Hi again,

Strictly speaking, the code in the workbook you posted should read as follows:

Please Login or Register  to view this content.

However, when you look at this code again six months from now, will you remember that "Sheets("Diary_Events")" and "Sheet3" actually refer to the same worksheet???

The code I posted originally avoids any such potential confusion.

As a result of having worked with VBA code for more years than I care to remember, my guiding philosophy when writing code is MINIMISE THINKING TIME AFTERWARDS.

Regards,

Greg M

Hi Greg

Thank you. That makes perfect sense, and I will bear it in mind with anything I work on in the future.

Darryl

Hi again Darryl,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M