Hide and lock cell formulas without affecting Macro?

[Mike]

Is there any way to hide and lock cells on a worksheet without affecting a
Macro? I have written a Macro that hides rows on a worksheet that have zero
value, I run the Macro through a form button. However, when I protect the
worksheet so the hide/lock feature takes effect, it takes away my ability to
run the Macro. Any ideas how to run my Macro but still be able to hide and
lock my cells containing certain formulas?

Thanks in advance.

 

[Pete_UK]

Your macro could unprotect the sheet, do its stuff, and then protect
the sheet again at the end. You will need to provide your password (if
used) within the macro.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, I forgot to tell you how to do it. Here's one way:

ActiveSheet.Unprotect Password:="Pete_1"

your existing code ...

ActiveSheet.Protect Password:="Pete_1"


Hope this helps.

Pete

 

[Mike]

Thanks for the suggestion Pete. However, I'm wondering if the cells can be
locked and hidden before the macro is run, or am I out of luck?

 

[Gord Dibben]

You want a macro to hide and lock cells on a protected sheet but you want those
cells hidden and locked before the macro runs to hide and lock those cells.

Have I got it right?

If so, why?

Just run the macro on an unprotected sheet then at the end of the macro, protect
the sheet.


Gord Dibben MS Excel MVP