Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) | |||
sheet protected but cells "cleared of all" become locked | Excel Discussion (Misc queries) | |||
"Live" Advanced Filter (for unique records) | Excel Discussion (Misc queries) | |||
Advanced filter formula for "contains="Sept" | Excel Worksheet Functions | |||
How do i enable "Group" & "Ungroup" in a protected sheet | Excel Discussion (Misc queries) |