Worksheet change VBA
 

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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Worksheet change VBA
 

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)


"Jennifer" wrote in message
...
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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Worksheet change VBA
 

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!

"Jennifer" wrote:

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
--
Though daily learning, I LOVE EXCEL!
Jennifer

Worksheet change VBA
 

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.

--
regards,
Tom Ogilvy

"Jennifer" wrote in message
...
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!

"Jennifer" wrote:

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
--
Though daily learning, I LOVE EXCEL!
Jennifer