Changing the criteria will not automatically re-work the filter.
In 123, this could be done by hitting , but Bill Gates knows you don't want or need this.
Instead, you have to write a macro using Here is part 2!
In this code, I demonstrate the two types of drop down lists, adding a combobox to one of the sheets on the fly.
I always have any "controlling" software open its own instance of Excel. You are going to save it later (per your comment), so you are just putting strain on the server/disc unnecessarily.
What would happen if an open instance were running a long macro (some of mine have run for DAYS - literally. There might be good reasons for this that you have cut out of your code.
Regards, John Davies to set zoom and other page setup properties As to your other comments they are much appreciated, however many of those lines were lifted right out of the Win32:: Ole tutorial. My intention is to have drop down lists in a worksheet which are populated with data from another workbook. I can't think of a scenario where it makes since to "write" data to a named range.
Maybe the only answer is to import all of the data from the other workbook into worksheets which I can set to hidden and then use advanced filters as you suggest with references to ranges within the hidden worksheets? In other words, manual data entry is done with in a single cell (or merged cells), not a named range.
Another possibility is that it will try to read the name of the range to filter from the given range, but that can't work either as it's a multi-cell range. As you may gather, I can't be sure what it is you are trying to do, but I don't think it's possible with Autofilter.
You could have a combobox - the sort of control you might put on a form or on the face of the spreadsheet - or you could use Data Validation of a cell.
The techniques are quite different when it comes to populating the list.
I haven't created a form on the fly, as I imagine any form you would want would be static.
You don't need to link the combobox to a cell, but it can be useful so I have demonstrated it.