Excel will not "show all" from an Advanced Filter on a sheet with protected cells.
 

Hi all,
I'm a group newbie so apologies in advance for any faux pas!

Question:
I have a worksheet with an advanced filter operating on it, and I want
to protect cells by password. Using the normal routes for protection
I've allowed access to the cells I want to be editable and all is
fine, except.......

My advanced filter is controlled by 2 commandbuttons I've put ont the
form, "filter" and "show all" as the sheet is to be used by computer
users non technicals. The filter button works fine when the sheet is
protected, but the "advanced filter show all" button hangs with error
"Showalldata method of worksheet class failed", and highlights the
line of code "Activesheet ShowAllData" attached to that button in Vb.

This results in my data not being unfiltered, and Vb pops up, scaring
the above mentioned users.

I've used the line "On Error Resume Next" to cover the error and stop
Vb coming up, but I can't "show all" until I unprotect the sheet!

Is there anyway I can allow an advanced filter show all on a protected
sheet?

I've tried using the "activesheet.enableautofilter = true", but this
doesn't work for advanced filters. Grr.

Any ideas, your help is much appreciated.
Yours,
Gareth Surgenor

Excel will not "show all" from an Advanced Filter on a sheet with protected cells.
 

Have your code unprotect the sheet, showall, reprotect the sheet.

--
Regards,
Tom Ogilvy

"Gareth Surgenor" wrote in message
om...
Hi all,
I'm a group newbie so apologies in advance for any faux pas!

Question:
I have a worksheet with an advanced filter operating on it, and I want
to protect cells by password. Using the normal routes for protection
I've allowed access to the cells I want to be editable and all is
fine, except.......

My advanced filter is controlled by 2 commandbuttons I've put ont the
form, "filter" and "show all" as the sheet is to be used by computer
users non technicals. The filter button works fine when the sheet is
protected, but the "advanced filter show all" button hangs with error
"Showalldata method of worksheet class failed", and highlights the
line of code "Activesheet ShowAllData" attached to that button in Vb.

This results in my data not being unfiltered, and Vb pops up, scaring
the above mentioned users.

I've used the line "On Error Resume Next" to cover the error and stop
Vb coming up, but I can't "show all" until I unprotect the sheet!

Is there anyway I can allow an advanced filter show all on a protected
sheet?

I've tried using the "activesheet.enableautofilter = true", but this
doesn't work for advanced filters. Grr.

Any ideas, your help is much appreciated.
Yours,
Gareth Surgenor

Excel will not "show all" from an Advanced Filter on a sheet with protected cells.
 

Update:=

Your advice worked, but I had to find a way of confirming if the
worksheet was already protected or not.

activesheet.protectionmode seems like the right command, but it doesn't
work correctly. I got round it by using activesheet.protectcontents,
which returns F or T depending on whether the sheet is protected.

Many thanks again, Gareth

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel will not "show all" from an Advanced Filter on a sheet with protected cells.
 

from help on protectionmode

True if user-interface-only protection is turned on.

So for me, that doesn't even appear to be the correct attribute to check.

There are four attributes that need to be checked if you want to see if a
sheet is protected, but protectcontents would definitely affect what you are
trying to do.

from help on protectcontents:
True if the contents of the sheet are protected.

--
Regards,
Tom Ogilvy

"gareth surgenor" wrote in message
...


Update:=

Your advice worked, but I had to find a way of confirming if the
worksheet was already protected or not.

activesheet.protectionmode seems like the right command, but it doesn't
work correctly. I got round it by using activesheet.protectcontents,
which returns F or T depending on whether the sheet is protected.

Many thanks again, Gareth

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!