I'm thinking this is an easy one but I can't get it to work for me. I have one work sheet that unfilters and then filters results on a cell change. I have two other worksheets though where I want to accomplish the same thing for cells on their respective pages but instead of on a cell change I'd like it to run each time the worksheet is clicked on.
Here's the code I'm using for the page than works on cell change. That works great.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("b12")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' first remove filter
ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7
' then apply it again
ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7, Criteria1:="<>0"
End If
End Sub
I'm sure this is simple I just can't seem to find the answer and am not great with this stuff yet.
Here's the code I'm using for the page than works on cell change. That works great.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("b12")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
' Display a message when one of the designated cells has been
' changed.
' first remove filter
ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7
' then apply it again
ActiveSheet.Range("A1:AE3200").AutoFilter Field:=7, Criteria1:="<>0"
End If
End Sub
I'm sure this is simple I just can't seem to find the answer and am not great with this stuff yet.