Worksheet change VBA

[Guest]

Ok I'm in need of some help:
On my worksheet i have a cell with a validation drop box. The following code
filters the page when the user selects a value from the drop box. Problem is
I want it also to clear cell contents in cell c4. I know how to write it to
clear the contents but once you type something in c4 then the filter runs
again thus clearing the contents again. I understand this means the macro
runs anytime something is changed in the workshet. How do I write it to work
only when the drop down box is used. That way the user can change things on
the worksheet without it running the macro. The following is my code thus far:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("ProduceData").Range("Q2").Calculate
Worksheets("ProduceData").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("ProduceData").Range("q1:q2"), _
CopyToRange:=Range("a7:f7"), Unique:=False
End If

End Sub

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
On Erroro Goto ws_exit
Application.EnableEvents = False
If Target.Row = 3 And Target.Column = 3 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("ProduceData").Range("Q2").Calculate
Worksheets("ProduceData").Range("Database") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("ProduceData").Range("q1:q2"), _
CopyToRange:=Range("a7:f7"), Unique:=False
Me.Range("C3").ClearContents
End If
ws_exit:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

It worked. Thanks a bunch!
Why when I protect the sheet does the filter not work? I added
Activesheet.unprotect to the vba and it still doesn't work.

Thank you!

 

[Tom Ogilvy]

Do you unprotect the sheet before you alter the autofilter?

You can also do

Activesheet.Protect UserInferfaceOnly:=True
ActiveSheet.enableAutofilter

this works with an existing filter but won't create a new one.