UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Automatically re-apply filter in Excel    
 
   
zahi
post Jan 2 2008, 03:09 PM
Post #1

New Member
Posts: 7



Hi and Happy New Year!
would like to automate re-applying the filter in excel. I'm currently using Excel 2007 and I'd like to hide the data in my "Arrival" column that says "Sold Out". If I uncheck the "Sold Out" selection in autofilter, it works. However, the filter doesn't refresh automatically whenever a field changes status to "Sold Out". I would have to re-apply the filter manually to hide it. Is there a way to automate this process so my sheet would automatically refresh the filter?
Thanks for your help.
Go to the top of the page
 
+
doctor9
post Jan 3 2008, 10:58 AM
Post #2

UtterAccess Editor
Posts: 12,332
From: Wisconsin



Welcome to Utter Access!
Here's some simple code for a macro that will need to be run every time a cell potentially changes to "Sold Out".
Ogot this by typing a few cells of sample data in column A, occasionally using the phrase "Sold Out" for the value. Then, I turned on the macro recorder and did a filter to hide the "Sold Out" rows.
CODE
  
Columns("A").AutoFilter Field:=1, Criteria1:="<>Sold Out", Operator:=xlAnd

When to trigger this code will depend on how your data is changed.
Hope this helps,
Dennis
Go to the top of the page
 
+
zahi
post Jan 3 2008, 11:20 AM
Post #3

New Member
Posts: 7



Hi Dennis,
Is there a way to run this macro automatically each time a cell changes to"Sold Out"? I want the "Sold Out" cell or row to disappear immediately after I name it "Sold Out".
Thanks
Go to the top of the page
 
+
doctor9
post Jan 3 2008, 02:00 PM
Post #4

UtterAccess Editor
Posts: 12,332
From: Wisconsin



Unfortunatley I'm don't think there's a way to detect which cell was previously occupied, and you can't determine it just by looking at the (new) current cell. For example, after typing "Sold Out" into cell G5, you might hit [Enter], which could take you to G6. Or, you might use the [Tab] key to move to H5. Or you might use your mouse to go to ANY cell, worksheet, or workbook.
If the change is happening because you are manually typing into the cells, I think you're limited to only two choices:
1. Create a button (command button or a toolbar button) that runs the code, and just manually click the button whenever you need to; whether that's right after you type "Sold Out" or just after doing a bunch of updates is up to you. This would reduce your re-filtering work to a single click, but it's not automatic.
2. Place the code in the Change event for the worksheet.
This would be automatic, but it would run EVERY time ANY cell is changed. The screen would basically flicker with each change you make to the sheet (regardless of whether the change was in the "Arrival" column or not), which could get annoying.
Dennis
Go to the top of the page
 
+
zahi
post Jan 3 2008, 06:23 PM
Post #5

New Member
Posts: 7



I would be interested in the 2nd option you mentioned. Let me explain the issue I'm having in details:
I have 2 table worksheets. One is the original data source and the other is just a copy of the original one. It's linked to it by = formulas, so whatever changes in the original sheet is reflected in the other one.
I have a filter set in the linked sheet that shows all data except where it shows "sold out" in a certain column. However, if the status of the the field changes to "sold out" in the original sheet, it will be automatically changed in the linked sheet. BUT, the row will still be showing. To remove it, I have to reapply the filter.
My goal is to add a code to the sheet that either reapplies the filter automatically when a field changes so the sold out won't show. I want it to be automatic and not triggered with any command. I would like the linked sheet to refresh even if it's closed without any need to open it and run a macro manually or type any key.
Please advise if you know any code that can be useful to me.
Thanks
Go to the top of the page
 
+
doctor9
post Jan 4 2008, 09:52 AM
Post #6

UtterAccess Editor
Posts: 12,332
From: Wisconsin



You haven't described HOW the cell data is changing. Are you manually typing the words "Sold Out" on the sheet with the original data source?
If so, then you might consider putting the code on the filtered sheet's Activate event, since you will only need to run the code when you switch your view to the filtered sheet. Otherwise, you can put the code in the original data source sheet's Change event. You'll need to append the name of the sheet before the Columns("A") bit, since the code will be running while you are working on a different sheet from the one that the code is altering.
Your last paragraph is a little confusing; the code I already provided should work, it's now just a matter of deciding which action will trigger it.
Dennis
Go to the top of the page
 
+
zahi
post Jan 4 2008, 12:49 PM
Post #7

New Member
Posts: 7



The cell data changes when Sold Out is typed in the original data source sheet, so the filtered link sheet updates the information automatically.
tried to add the code in the Activate event of the filtered sheet but it's not working. My goal is have the filter reapplied automatically in the linked sheet each time "Sold Out" is entered in the data source.
How do I append the name of the sheet before Columns (A)? I'm not familiar with VB so I don't know how to write the syntax. I would be grateful if you could provide more details.
Thanks
Zahi
Go to the top of the page
 
+
doctor9
post Jan 4 2008, 01:46 PM
Post #8

UtterAccess Editor
Posts: 12,332
From: Wisconsin



Ah, sorry about that. I was assuming too much.
Here's how to add the code:
1. Open the Visual Basic Editor (Alt-F11).
2. If necessary, open the Project Explorer window in the editor (Ctrl-R).
3. Find the worksheet where you actually type the word "Sold Out" in the explorer, and double-click it.
A blank window will open in the main area. At the top, the word "(General)" appears in the upper-left combo box and the word "(Declarations)" appears in the upper-right combo box.
4. Change the upper left combo box to "Worksheet".
Two lines of code text will appear in your editor. You don't want them, so delete them. The upper right combo box now reads "SelectionChange". This is CLOSE, but not what you want.
Change the upper right combo box to "Change".
Two more lines of code text appear. This time, keep them. This is the header and footer of the macro that will run every time you make a change in the worksheet you double-clicked on in step #3.
Add one line of code BETWEEN them so your code window reads like this:
CODE
  
Private Sub Worksheet_Change(ByVal Target As Range)
'   Update the filtered data after every change made in Sheet1
    Sheets("Sheet2").Columns("A").AutoFilter Field:=1, Criteria1:="<>Sold Out", Operator:=xlAnd
End Sub

You'll want to replace "Sheet2" with the name of the sheet that contains the FILTERED data. You'll also probably want to change the column from "A" to whichever column is being filtered on.
That's it. Test it out and see if it behaves the way you want it to. I've attached a small demo that includes the working code so you can study it, if necessary.
Hope this helps!
Dennis
Attached File(s)
Attached File  AutoUpdateFiltersDemo.zip ( 7K ) Number of downloads: 15
 
Go to the top of the page
 
+
zahi
post Jan 4 2008, 03:39 PM
Post #9

New Member
Posts: 7



Each sheet in my case is a different file in a different location. How would I write a code to refer to them?
Thanks a lot
Go to the top of the page
 
+
doctor9
post Jan 7 2008, 11:50 AM
Post #10

UtterAccess Editor
Posts: 12,332
From: Wisconsin



I think this is the first time you've mentioned more than one sheet being filtered.
On any case, there's no point in looping through a series of files and applying a filter to a sheet in them if you're not looking at all of the filtered sheets right now, as you're changing the data in the source data sheet. Do the filtering when you go to look at the filtered sheet instead.
I would recommend putting code in each sheet's Activate event, as I suggested earlier. That way the filtering happens when you go to look at the sheet.
Dennis
Go to the top of the page
 
+
zahi
post Jan 7 2008, 01:00 PM
Post #11

New Member
Posts: 7



1 sheet is being filtered.
I have 1 source sheet in a file and the filtered linked sheet is saved as a different file.
I would like to be able to filter out everything that's "Sold Out" whenever I open the linked sheet, even if the source sheet is closed.
The persons that will be working on the source sheet will not open the linked sheet, and vice versa with the other people working on the linked sheet.
The activate event is only working if I click on the play button in VBA which runs the Sub/Userform. It's not updating the sheet automatically.
Please advise as I'm getting close to the solution.
Thanks
Go to the top of the page
 
+
doctor9
post Jan 7 2008, 01:50 PM
Post #12

UtterAccess Editor
Posts: 12,332
From: Wisconsin



It seems like we're having a communication problem. If you click on the line of code for filtering your filtered data worksheet, the upper left combo box should read "Worksheet" and the upper right combo box should read "Activate".
If you want the filtering to happen whenever you "open the linked sheet", the Activate event is the way to trigger it. The worksheet's Activate event fires every time you open the sheet.
However, this does NOT mean that you will see live updates happening as you watch the screen. The code only triggers when you open the sheet. You can click on another sheet within the same workbook and come back to the filtered sheet to trigger Activate. It will also fire the first time you open the workbook, if the filtered sheet is the one that is active at the time.
It will NOT fire by just going from Excel to another program and back again. Also, it will not fire while you are looking at the filtered data and someone else types "Sold Out" in the source data sheet. If this is what you're looking for, I'm afraid you're asking Excel to do things it's not really designed to do.
Dennis
Go to the top of the page
 
+
zahi
post Jan 8 2008, 09:43 AM
Post #13

New Member
Posts: 7



It worked when I put the codes in the workbook form not the worksheet form.
Thanks a lot for your help and patience!
Zahi
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 30th August 2014 - 03:24 PM