Easy Question I'm sure. Filter Results on each page open

thiess22

New Member
Joined
Mar 25, 2022
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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.
 
No errors. It does nothing.

The filter code I pasted in my first message that changes on the b12 cell change works just find. I jsut can't seem to marry it up with your code for the worksheet selection activation
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Did you check to see if the code is executing?

Try this. Change this one line:
VBA Code:
Private Sub Workbook_SheetActivate(ByVal SelectedSheet As Object)

to two lines:
VBA Code:
Private Sub Workbook_SheetActivate(ByVal SelectedSheet As Object)
Msgbox SelectedSheet.Name

In other words, add that second line to the code and see what happens when you change sheets.
 
Upvote 0
I did this code below. Nothing Happened.

Private Sub Workbook_SheetActivate(ByVal SelectedSheet As Object)
MsgBox SelectedSheet.Name

If SelectedSheet.Name = "JOB ESTIMATE" Then


ActiveSheet.Range("C11:E702").AutoFilter Field:=1

ActiveSheet.Range("C11:E702").AutoFilter Field:=1, Criteria1:="<>0"



End If

End Sub
 
Upvote 0
It sounds like you don't have the code pasted to the right module. Where do you have the code located? In which module?
 
Upvote 0
I just had it pasted into Sheet 25 (JOB ESTIMATE) under Microsoft Excel Objects That's how I had pasted the other formula i first posted here that works
 
Upvote 0
Please go back and read post #6 & post #7 which gives the directions/location of placement.
 
Upvote 0
Oh my goodness. Thank you so much! And thank you for your patience with me!
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top