Advanced Filter not clearing in locked sheet - Excel 2007

[mrstaggart]

I am making a form for the sales team.

I have a drop down to select a product. With a macro attached to a button, the macro filters an item list based on the selected product. (Similar to this thread as a start point:

http://www.tek-tips.com/viewthread.cfm?qid=1632093)

The form is locked, but the drop down cell for selection is not (obviously).

When selecting a value like "Fruit" or "Dairy", it works great. When trying to select blank it fails -- MS VB "Run time error '1004': ShowAllData method of Worksheet class failed". With the form locked, I also noticed in the Data ribbon - Sort & Filter group - "Clear" option is gray.

The macro text follows, where C9 is my select-box-result-cell for product (<blank>,Fruit, Dairy) and my filtered data is in cell range A13 to H250:

Sub Product_List()
'
' Product_List Macro
' If Pricing for Product is blank, it unfilters all; if not blank, filters for matches in Col B

'
If Range("C9") = 0 Then
ActiveSheet.ShowAllData
Range("A1").Select
Else
Range("A13:H250").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("C8:C9"), Unique:=False
End If
End Sub

 

[SkipVought]

Hi,

What kind of drop down control:
1. Data Validation
2. MS Forms
3. ActiveX

What are your list elements? Please paste entire list.

What value in C9 for the a <blank> selection?

Please answer each of these questions.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrstaggart]

Good questions - sorry I missed them before:

drop down control is Data Validation.
Blank is literaly blank - the validation looks at range AA1:AA5 which read:
AA1
AA2 Dairy
AA3 Fruit
AA4 Meat
AA5 Grain

 

[SkipVought]

how about this...

         If Range("C9") = 0 Then
            ActiveSheet.Unprotect
            ActiveSheet.ShowAllData
            ActiveSheet.Protect
            Range("A1").Select
         Else
'...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrstaggart]

I was hoping to avoid the unprotect/reprotect option (I do it elsewhere in the form).

Alas. Thank you so much for thinking through my issue - I really appreciate it.

 

[SkipVought]

I was hoping to avoid the unprotect/reprotect option

WHY? Its just code!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}