Hide and lock cell formulas without affecting Macro?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 22 May 2008 00:26 GMT

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.

Reply to this Message

Pete_UK - 22 May 2008 00:35 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> Thanks in advance.

Reply to this Message

Pete_UK - 22 May 2008 00:47 GMT

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

> 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
[quoted text clipped - 14 lines]
>
> - Show quoted text -

Reply to this Message

Mike - 23 May 2008 20:37 GMT

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?

> Sorry, I forgot to tell you how to do it. Here's one way:
>
[quoted text clipped - 26 lines]
> >
> > - Show quoted text -

Reply to this Message

Gord Dibben - 23 May 2008 23:53 GMT

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

>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?
[quoted text clipped - 29 lines]
>> >
>> > - Show quoted text -

Reply to this Message