AdvancedFilter - Criteria Named Range on Different WorkSheet - No Records Returned (except with bizarre MsgBox workaround)

Which version of Excel, and is your userform modal or modeless?

Rory, thanks for replying.

__________________________
Excel 2003 (11.8169.8172) SP3
Office Professional Edition

VBA 6.5
Version 1024
VBA: Retail 6.5.1024
Forms3: 12.0.6025.500
__________________________

The form is Modeless.

It is called/loaded by an embedded Excel CommandButton Control on the "STATS" Worksheet.

In case it's relevant, I'll mention that during the Userform_Initialize / Initialise event, I had to temporarily Disable Events...

First line in the Userform's Module in "General / Declarations" is:

I had to Disable Events because I was getting an error when loading it (?maybe "Improper Use of Null" - I can't remember for sure).

The long story, if you've time is that the two ComboBoxes that I refered to in this post...

http://www.mrexcel.com/forum/showthread.php?t=403235

...required the same Data to be loaded into each of them, but sorted in a different order.

Initially, I tried to do it with two Arrays, sorted with Bubble Sort loops, but that took 8 seconds.

So I returned to using cut & paste and sort on the "LISTS" Worksheet, which took 0.1 seconds for 4 Data Tables (the 2 extra sort orders were for posssible future controls).

No competition!

Anyway, this routine was in the Initialize Event Procedure. But, since the newly created and sorted Data Tables were also the RowSources for the ComboBoxes, when I stepped through with F8 (I like your slogan by the way!) I noticed that the Change Event of the ComboBoxes was being triggered.

If relevant, the cut & paste was from 1 Named Range to 4 other Named Ranges.

Long Story. Thanks for listening!

Might have been easier if I'd just posted the code, but I'd have to edit it to make it look presentable (too many "notes to self" on this steep learning curve!)

Any chance you could post the workbook somewhere, or email it? (removing any confidential data)
As a general point, I recommend not binding controls to worksheets - causes far more problems than it solves. It's easy enough to assign a range to the List property of a combo or listbox.

Named ranges are not properties of sheets, i.e. ActiveSheet.Range("someName") is questionable. Try removing the sheet qualifications in this

Thanks again for your interest and offer of assistance. Much appreciated.

Re: Emailing Workbook:
Tricky to do so. Lots of confidential info in it at the moment.
Will try to see if I could produce a version with just the structure minus the data.
The spring clean might do it some good in itself.
But, as Laurence Oates once said (in very different circumstances) "I may be gone for some time"!
If and when it's ready, is it possible to email it to you via this site?

Re: Binding:
Are you suggesting that, rather than assigning the Named Range to the RowSource Property at Design Time, it could be done at Runtime, and that this might be better?
I presume it would be done during the Userform_Initialize Event too then.
Do I need to unassign the Range later (Set RowSource to Null) (eg when form Unloads, assuming it doesn't crash before then)?

Re My VBA Skill Development:
I'm starting to think I need a succinct best practice guide. I have found several good resources like this site, and [http://www.cpearson.com/excel/topic.aspx] and I've read a book called Excel Programming by Jinger Simon.

These have been invaluable for tackling immediate problems, and specific details; but I also need to develop the ability to see the bigger picture, and to know how to structure the relationship between different modules and events and prepare for the unexpected with errorhandling...
(eg when to dimension variables, when to initialise them, and when to "unset" them; when to bind and when not to; when to use arrays and when to use ranges)

If you have any suggestions re best practice guides, programming principles, typical code / procedure flowcharts etc, or even online training, I'd be happy to hear about them.

Cheers!

Mike,

Thanks for the tip.

Think I might have tried that amongst the millions of deleted attempts. Will give it a shot though. Thanks.

Hi Mike (and Rory)

OK. Tried Mike's suggestion...

Re: Specifying Sheet Name (or Not):
It did work as well (or as badly) as the With ActiveSheet statement, but I'm afraid it only worked if I used the nonsensical messagebox. Without it, no records were returned again!

I think that one of the reasons I wanted to specify the Sheet Name, was that the Data Entry (aka "STATS") Sheet, will be one of 12 identical monthly statistics recording forms. I was planning on using the same Name for the sort area Named Range on each sheet, and permit the user to carry out operations on whichever sheet they choose, flicking from one to another without closing the form.

I'm fairly sure Excel allows this, as long as you specify the sheet name as part of the Name definition, which it does by default when creating a name.
Also if you have a named range on a worksheet and then copy-insert the sheet in the same workbook, you end up with two identically named ranges.

The reason why I specified the "LISTS" sheet when refering to the Criteria Range was that (as far as I remember), I was getting an error (perhaps "object not defined" - sorry can't remember exactly). However, I did NOT get that (or any) error message on this most recent test.


Re: Reliability, Consistency and Speed of Control Appearance Update:
Incidentally, Clicking either of the 2 OptionButtons seems to change The Selected Control's Value to True, and certainly the Criteria Table Cells change their Values to reflect this change.

BUT: The Black Dot in the centre of the OptionButton Control does not always move to the Selected Control on first press. Sometimes it stays with the previous option selected, until it is clicked a second or even a third time. Often it does move on first press but it moves "slowly" (eg 0.5+ seconds).


PS: Thanks a million for the input. Need to get some sleep though! So if you don't see any response from me for a few hours, that's why! Cheers for the help.

Default300 said:

Re: Emailing Workbook:
...
If and when it's ready, is it possible to email it to you via this site?

Click to expand...

PM me when ready and I'll send you an email address.

Re: Binding:
Are you suggesting that, rather than assigning the Named Range to the RowSource Property at Design Time, it could be done at Runtime, and that this might be better?
I presume it would be done during the Userform_Initialize Event too then.
Do I need to unassign the Range later (Set RowSource to Null) (eg when form Unloads, assuming it doesn't crash before then)?

Click to expand...

Not quite. I'm saying don't use the RowSource at all. At runtime you can load the range into an array and assign that to the List property.

Re My VBA Skill Development:
I'm starting to think I need a succinct best practice guide. I have found several good resources like this site, and [http://www.cpearson.com/excel/topic.aspx] and I've read a book called Excel Programming by Jinger Simon.

If you have any suggestions re best practice guides, programming principles, typical code / procedure flowcharts etc, or even online training, I'd be happy to hear about them.

Click to expand...

Lots of good books. Professional Excel Development is the Bible really, but parts of it are pretty advanced. John Walkenbach and Bill Jelen both have Excel VBA programming books, and there's the Wrox Press Excel 200n VBA Programmers Reference series (don't get the 2003 one). I would also recommend the VBA Developers Handbook from Sybex - it's 'pure' VBA rather than Excel specific, but very comprehensive.

Obviously, those are a matter of personal preference!

Hi Rory.

I've been busy overhauling this project, and reading more re VBA. Still not in a fit state to send you the entire project.

Re: Range to Array to ComboBox.List Property:

At runtime ... load the range into an array and assign that to the List property

Click to expand...

I think I'll try your suggestion.

[?] When should I do both? I'm presuming during the Initialize Event?

[?] Where should I place the Dim statements for each Array Variable?
Since they will be used by the Control to which it is assigned as List property, do they need to be outside the Initialize Event / Procedure (perhaps at the top of the Userform Module, after Option Explicit Statement?)




Re: General Confusion:
In general, I find this aspect of VBA the most confusing, ie When and Where to Dimension & Initialise Variables, in order to make sure they are available when required.

The other aspect is designing the Flow of the Code. Using F8, I was suprised to see how I was triggering change events in various controls just by setting their default values. I was considering using a variable defined at start of Userform Initialize Event, and redefined after, to prevent routines in Control Event procedures from firing until Userform is fully initialised. Or perhaps to use the form's Tag property. "EnableEvents = False" (at the start) and "True" (at the end) does not seem to "stop" the procedures (as seen with F8 step through).

Re: Sub Main / Form Launch Code:
Incidentally, I do not have a Sub Main as such, though the code behind the ToggleButton embedded in the worksheet (which launches the form) seems to be very similar to what I've seen.

Re: ControlSources, Binding & Passing Data To & From Forms:
One more thing I'm considering doing, (inspired by your suggestion quoted above) is to stop using worksheet ControlSources with all the other controls (Checkboxes, OptionButtons, and Comboxes), but to preserve the user's settings between sessions by saving their Values on a worksheet (on closing the form), and reloading them into Value properties (not ControlSources) on Form load.

Linked to this, another question I'm asking my self is when and where to initialise these controls' values. Should it be in the Initialize Event, or in the Worksheet Module which (my Sub Main equivalent), perhaps using Property Let and Later Get procedures.

Thanks to these two people for advice on passing variables to & from forms:
http://www.dailydoseofexcel.com/archives/2004/07/22/passing-arguments-to-a-userform/
http://peltiertech.com/Excel/PropertyProcedures.html


If you (or others) think I should try to split this into two or more posts, then let me know.
I find it difficult to know where to draw the line since all problems seem to be interlinked until I know the solution.


I'm starting to think my slogan should be:
"A little knowledge is a dangerous thing - which makes me the most dangerous man alive!"