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

Default300 · Jul 17, 2009

Can anyone explain why this code only works with the message box inserted?

And can you help me avoid having to have that line.

The code is the _click event code behind an OptionButton on a Userform.
The alternative OptionButton in the 2 button group removes all filters with: ActiveSheet.ShowAllData

Both OptionButtons are Bound to Separate ControlSources on the "LISTS" Worksheet.

I think I use standard RVBA naming convention, except for Worksheet Named Ranges, for which I use the improvised "nam" prefix, eg "namSomeText".
(Incidentally, if there is a standard convention for this I'd be happy to learn about it.)

Code:

Private Sub optFiltersAllActivatedEnable_Click

Code:

'(AIM)  Enable All Selected Filters
'       (Show Only Records Which Match One Or More Criteria Selected)
 
'(i)    Ensure Routine will Only Run If ActiveSheet is a "STATS" Sheet
 
If Left(ActiveSheet.Name, 5) <> "STATS" Then
 
MsgBox "Please Select a ""Statistics"" Worksheet First"
Exit Sub
 
End If 
 
Application.ScreenUpdating = False
 
 
'[***] [BUG] AdvancedFilter WILL NOT WORK,
'            UNLESS a MesssageBox Appears at this point!
'            Without it, No Records are returned.
'            Text of Message is irrelevant.
 
MsgBox "Random Text"
 
 
'(i)    Apply AdvancedFilters
'       Range to Filter is a Named Range: "namSheetDataEntrySortAreaInclHeader"
'       Range to Filter is on a Worksheet called: "STATS (MAR 09) (Print) (5)"
'       Filter Criteria Range is on Worksheet called: "LISTS"  (in Same Workbook)
 
 
With ActiveSheet   
 
.Range("namSheetDataEntrySortAreaInclHeader").AdvancedFilter _
   Action:=xlFilterInPlace, _
   CriteriaRange:=Worksheets("LISTS").Range("namListsCriteriaRange"), _
   Unique:=False
 
End With
 
 
'(i)    Scroll To First Row In Data Entry Sheet Interventions Area
 
ActiveWindow.ScrollRow = Range("namSheetDataEntryInterventionsAll").Row 
vsbNavigateScrollVertical1.Value = ActiveWindow.ScrollRow
 
 
'(i)    Move Cursor To "Safe" Blank Cell
 
Range("namSheetDataEntrySafeCell").Select
Application.ScreenUpdating = True
 
End Sub

The following is a selection of previous attempts at coding the AdvancedFilter.
Some permutations trialled are not shown as they were deleted...

Code:

' With Sheets("STATS (MAR 09) (Print) (5)")
 
' .Range("$A$6:$CY$891").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Worksheets("LISTS").Range("$AT$5:$AV$11"), _
Unique:=False
 
' Range("namSheetDataEntrySortAreaInclHeader").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("namListsCriteriaRange"), _
Unique:=False

Thanks in advance.

RoryA · Jul 28, 2009

OK, let's take those one by one!

Re: Range to Array to ComboBox.List Property:
Quote:
At runtime ... load the range into an array and assign that to the List property
I think I'll try your suggestion.
[?] When should I do both? I'm presuming during the Initialize Event?

Correct.

[?] 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?)

As a general rule you should give variables the most limited scope they require. In this case (assuming you are not reusing them in any other procedures) declare them within the Initialize event. Once assigned to the List property, that property will maintain the values until the form is unloaded (or you reset the control).

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.

As mentioned above, variables should have the most limited scope required. If you only use a variable within one procedure, declare it within that procedure. If shared between multiple procedures, then you have three options:
1. Pass the variable between procedures as an argument if possible.
2. If all the procedures are in the same module, declare the variable at the top of the module, outside any procedures, using Private or Dim.
3. If procedures in different modules require the variable, and option 1 is not practical, declare the variables as Public at the top of a normal module. Try to limit the number of public variables you use as much as possible.

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).

EnableEvents does not work with forms. The normal method is to use a boolean variable declared as private at the top of the userform module. Set it to True when you want to disable event processing and then back to False afterwards. Then each control event starts with a simple check of the variable - if it's True, then exit. For example:

Code:

Private Sub TextBox1_Click()
   If blnSkipEvents Then Exit Sub
   ' normal processing code here
End Sub

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.

Sub Main is more a VB thing than VBA. You will most usually see code like:

Code:

Userform1.Show

to launch a form. It's actually better to use a variable:

Code:

Dim frm As Userform1
Set frm = New Userform1
frm.Show
Unload frm

as it makes it easier to debug your code. Using a direct reference to Userform1 will create a new instance of the form if yours had terminated abnormally for instance, and you will find yourself wondering why you get no errors with the code but none of your inputted values are present. Note that if you do this, you need to prevent your users from unloading the userform by clicking the X in the top corner - trap the QueryClose event and ensure you always hide the form. Your calling code can then check any values you want and unload the form.

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.

Generally the Initialize event of the form is the best place to load initial values. If you hide and show the form a lot, and you want to reset each time, then you could use its Activate event instead.

Default300 · Jul 30, 2009

Appreciation & Thanks:
Hi Rory. HTH is an understatement! More like a free "Get Out Of Jail Card". Extremely valuable info. I have read it, but haven't had time to assimilate it or try using it yet. Just wanted to let you know that I'd seen it, and to thank you for taking the time to explain it. Will post back when I have something significant to say!

Changing A Thread Title:
[?] Incidentally, can the original poster (or anyone else, besides a moderator) change a Thread Title if it veers a long way from the original question? Or do fora/forums tend to leave it once set, to allow others to refind it?

Default300 · Oct 19, 2009

Hi Rory et al.

Preamble
I had been sticking my head in the sand about this bewildering AutoFilter problem, and had been concentrating on a parallel project (which was more problem free), until recently I forgot to bring the latter home to work one night, and so was forced to tackle this one again...

AutoFilter Problem & Temporary Curative Effect of F8
In an attempt to problem solve, I stuck a breakpoint on (or ?just before) the AutoFilter line of code. Then I stepped through with F8.

Strangely, the problem no longer occurred (ie the AutoFilter worked correctly). When I removed the breakpoint (and let code run normally, without F8), the problem reoccurred! Aaagh!

AutoFilter Problem & ?Resolution with Unbound Controls
At a loss, I decided to focus on Unbinding the Controls all the controls from their ControlSources as discussed in posts above.

(Incidentally, the ControlSources and AutoFilter Criteria Table had been on the same Worksheet. The Data to be filtered was on a second Worksheet in the same Workbook. The AutoFilter Criteria were Formulae, dependent on the values of the ControlSource Cells. Each ControlSource Cell was a Global Level Named Range. The Criteria Formulae referred to the relevant ControlSource Cell using its Name, not a direct reference. All three used Conditional Formatting, but testing with plain unformatted text made no difference.)

I made all Controls Unbound (no ControlSources).

But, I used each Control's Change Event procedure to copy its new Value to the same cell that used to be its ControlSource.

I made no changes to the Criteria Table.
I made no changes to the Data Table.
I made no changes to the Named Ranges (ex ControlSource linked cells, Criteria Table, or DataTable). (All Global Names as in original post; though also successfuly tested with Local on Data Table.)

I tested three versions of Code for the Advanced Filter that were unsuccessful before:

(1) This version is same as one of the two deleted pieces of code in my first post and (as Mike Rickson suggested) does not refer to activesheet...

Code:

Range("namSheetDataEntrySortAreaInclHeader").AdvancedFilter _
        Action:=xlFilterInPlace, _
        CriteriaRange:=Range("namListsCriteriaRange"), _
        Unique:=False

(2) This one is as in my original post...

Code:

With ActiveSheet
 
.Range("namSheetDataEntrySortAreaInclHeader").AdvancedFilter _
   Action:=xlFilterInPlace, _
   CriteriaRange:=Worksheets("LISTS").Range("namListsCriteriaRange"), _
   Unique:=False
 
End With

(3) This one is as in (2), but does not include the qualifier [ Worksheets("LISTS"). ]

Code:

With ActiveSheet
 
.Range("namSheetDataEntrySortAreaInclHeader").AdvancedFilter _
   Action:=xlFilterInPlace, _
   CriteriaRange:=Range("namListsCriteriaRange"), _
   Unique:=False
 
End With

Result:
All 3 versions of code work!

And they work with either Global or Local Names defined for the Data Table

("namSheetDataEntrySortAreaInclHeader").

Plan Now:
Therefore, I plan to use version (3), and use Local Names for the Data Table, so that I can have 12 monthly Data Tables (one per worksheet) and flick from to another without closing the form.

I may also give each sheet its own Criteria Table linked to its own list of Settings for each Control. That way I hope I can load each sheet's settings into the UserForm using the Worksheet_Activate Event as a trigger. Though, I haven't fully thought through the potential pitfalls and complications of this yet.

Re: Range to Array to ComboBox.List Property:
Regarding the ComboBoxes mentioned in previous posts, I have not yet changed over to Loading the Named Range into an Array and assigning that to the ListSource of the Control (as per Rory's post #9). But this suggestion is what inspired the approach that turned out to solve the other problem!

Thanks again Rory and Mike.

Next Question:
Now this leads me to ask a new question.

But, since my initial problem appears to have been resolved, I'll ask the follow-on question in a new post, and post a link to it here.

I presume it is better to post replies to the new question there rather than here.

The jist of the question will be:

Regarding Advanced Filter:
Must the Criteria Table be Written to and Read from a WorkSheet?
Or can the Criteria be Contructed in and held in Code?

For Constructing the Criteria:
I'm thinking of somehow using the Formulae I used in the the criteria table cells, perhaps even worksheet functions.

For Holding the Criteria in a Matrix (if this is required):
I'm thinking of using a two dimensional array variable.

But maybe this transalation from Excel to VBA is too literal.

"Answers on a postcard to..."

Title:
Post ID:

Much appreciated!

Re: This Forum & Icons:
Several useful icons, including Lightbulb, Question Mark, Thumbs Up & Down, and Arrow are available only as post icons but are sadly missing from the smilie section. I think they could make posts easier to read, especially for very wordy ones like mine. They help the reader scan to the points most relevant to them.

Default300 · Oct 19, 2009

I seem to be unable to edit my previous post, so here's the link to the next question...

New Thread Title:
Advanced Filter: Criteria: ?Create & Hold in Code; OR ?Is Worksheet Table Essential

New Thread (Link to First Post):
http://www.mrexcel.com/forum/showpost.php?p=2094402&postcount=1

mikerickson · Oct 19, 2009

About triggering Userform control events:

Setting Application.EnableEvents to False / True does not effect userform events.

Instead, I declare a module wide Boolean variable at the beginning of the userform code module.

And then put a testing line at the start of each of my event routines.
For example:

Code:

Dim ufEventsDisabled As Boolean

Private Sub ComboBox1_Change()
    If ufEventsDisabled Then Exit Sub
    
    ufEventsDisabled = True
    TextBox1.Value = ComboBox1.Text
    ufEventsDisabled = False
End Sub

Private Sub TextBox1_Change()
    If ufEventsDisabled Then Exit Sub
    
    MsgBox TextBox1.Text
End Sub

Private Sub UserForm_Initialize()
    ufEventsDisabled = True
    With ComboBox1
        .AddItem "a"
        .AddItem 22
        .AddItem "xyz"
    End With
    TextBox1.Text = "none"
    ufEventsDisabled = False
End Sub

Default300 · Oct 22, 2009

Hi Mike,

Thanks for coming back to this and offering your assistance.

Rory gave a similar tip in post 11 above. (Easy to miss in the midst of my long posts and his comprehensive reply.)

I have implemented it successfully for the Userform_Initialize event.

Thereafter, occasionally I need to stop controls from triggering events with each other.

I had been planning to use a second (almost identical) variable to stop this occuring, but am wondering whether in your experience there is ever a need for two?

I just thought it would be better to distinguish Initialize event from other situations, in case there was a situation where I did want one or more controls to automatically trigger each other.

It's the fear of the unknown that makes the novice take the cumbersome, uncomfortable (and not very aesthetically pleasing) belt and braces approach!

mikerickson · Oct 22, 2009

If you had two routines that changed the .ListIndex of a list box, both routines would trigger both the Click event and the Change event.

If you wanted one of those routines to trigger only one of the events and the other to trigger neither of them, then you would need two eventDisable variables.

Other than very oddball situations like that, I don't see the need for two eventDisable variables.

Default300 · Oct 22, 2009

Thanks.

Unfortunately, as an untrained / self trained novice I'm master of the oddball situation creation team.

Definitely improving though experience, help files and advice.

But still find it hard to get an overview of the whole project.

Doing things piecemeal, and by trial and error. Then, if they work applying them to each procedure, occasionally missing one accidentally.

Cheers. Goodnight.

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

Default300

Board Regular

RoryA

MrExcel MVP, Moderator

Excel Facts

Default300

Board Regular

Default300

Board Regular

Default300

Board Regular

mikerickson

MrExcel MVP

Default300

Board Regular

mikerickson

MrExcel MVP

Default300

Board Regular

Similar threads

Forum statistics

Share this page

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

Board Regular

MrExcel MVP, Moderator

Excel Facts

Board Regular

Board Regular

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock