I have a macro that hides rows based on 0 value in certain rows in a single
column using autofilter. I have as a convenience, protected two columns of
data to the left of the sorted column so that user can just use the enter key
to pass over the two columns to the next allowable editable cell. But because
I have locked the cells in those columns, my macro will not collapse the
sheet based on 0 values next to the cells that are locked. My feeling is that
I won't be able to get around this. Or can I?...

You could add an unprotect line to your code, do the collapse then re-protect
the sheet.

Sub foo()

ActiveSheet.Unprotect Password:="justme"
'your code rund here
ActiveSheet.Protect Password:="justme"

End Sub


Gord Dibben MS Excel MVP

On Tue, 6 May 2008 07:53:00 -0700, John G.
wrote:

I have a macro that hides rows based on 0 value in certain rows in a single
column using autofilter. I have as a convenience, protected two columns of
data to the left of the sorted column so that user can just use the enter key
to pass over the two columns to the next allowable editable cell. But because
I have locked the cells in those columns, my macro will not collapse the
sheet based on 0 values next to the cells that are locked. My feeling is that
I won't be able to get around this. Or can I?...

My brain gets muddled and can't see the forrest for the trees. There is
always a work around. Thanks!

"Gord Dibben" wrote:

You could add an unprotect line to your code, do the collapse then re-protect
the sheet.

Sub foo()

ActiveSheet.Unprotect Password:="justme"
'your code rund here
ActiveSheet.Protect Password:="justme"

End Sub


Gord Dibben MS Excel MVP

On Tue, 6 May 2008 07:53:00 -0700, John G.
wrote:

I have a macro that hides rows based on 0 value in certain rows in a single
column using autofilter. I have as a convenience, protected two columns of
data to the left of the sorted column so that user can just use the enter key
to pass over the two columns to the next allowable editable cell. But because
I have locked the cells in those columns, my macro will not collapse the
sheet based on 0 values next to the cells that are locked. My feeling is that
I won't be able to get around this. Or can I?...

Happy to help.

Thanks for the feedback.

I think "rund" was to be "runs"


Gord

On Tue, 6 May 2008 13:48:34 -0700, John G.
wrote:

My brain gets muddled and can't see the forrest for the trees. There is
always a work around. Thanks!

"Gord Dibben" wrote:

You could add an unprotect line to your code, do the collapse then re-protect
the sheet.

Sub foo()

ActiveSheet.Unprotect Password:="justme"
'your code rund here
ActiveSheet.Protect Password:="justme"

End Sub


Gord Dibben MS Excel MVP

On Tue, 6 May 2008 07:53:00 -0700, John G.
wrote:

I have a macro that hides rows based on 0 value in certain rows in a single
column using autofilter. I have as a convenience, protected two columns of
data to the left of the sorted column so that user can just use the enter key
to pass over the two columns to the next allowable editable cell. But because
I have locked the cells in those columns, my macro will not collapse the
sheet based on 0 values next to the cells that are locked. My feeling is that
I won't be able to get around this. Or can I?...