Solved: Working with a Filtered Dataset

[Opv]

The attached sheet is a sample of a set of Greek flash cards. In summary, the workbook consists of two sheets, a "database" of terms/phrases and a "practice" sheet which pulls in one word at a time from the database and allows me to practice writing (typing) and saying the Greek term that goes with the English term displayed on the practice sheet. Now for the specific pieces. The functionality incorporated into the workbook consists of the following scripts:

1. A defined Name: =OFFSET(Database!$A$3,1,0,COUNTA(Database!$A:$A)-1,3)

MODULE ONE:

2. A Randomize script which is executed when I click on the appropriate button within the Practice sheet. This script simply randomizes and ranks the index for the database so that the terms can be pulled into the Practice sheet in a different order.

3. A findNext script which tracks how many terms/phrases are remaining to be displayed.

4. A GetNext script which actually cycles through the ordered (ranked) items and pulls in and displays the next appropriate term from the Database.

MODULE TWO:

5. a showGreek script which temporarily displays the Greek term if I determine I need it.

6. a hideGreek script which automatically hids the Greek term after 5 seconds.

PRACTICE SHEET:

7. A Worksheet_Change script which automatically calls the next term/phrase ONLY if I correctly type in the Greek term to go with the English word/phrase displayed.

Below the row on which the practice data is displayed, I have arranged a series of cells to display the descriptors for the term/phrase displayed, i.e., Case, Gender, Number, Tense, Voice, etc. These cells are populated using VLookup.

Now, what I am wanting to do is make all of the above script continue to function as they currently do even if I have the Database in Auto Filter mode. For example, there are occasions where I need to focus on specific words/phrases more so than others. I would like to be able to filter the database so that only the visible words/phrases are displayed. Any help will be greatly appreciated.

Thanks,

Opv

[Opv]

I forgot to mention that most of the current functionality is provided courtesy of mdmackillop.

[Opv]

I am open to suggestions if the desired functionality is not possible.

Thanks,

Opv

[Opv]

I think I have figured out a solution. I'll mark the thread "Solved" after I do a bit more testing.

Opv

[Aussiebear]

Please be kind enough to post the solution when you've arrived at it.

[Opv]

OK. I have all of the scripts doing what I want, except one tiny kink. Actually, this problem doesn't effect the overall functionality of the workbook. It's more of procedural flaw that is just a bit irritating. The issue relates to a subroutine which counts the number of remaining terms and then populates the appropriate range in the practice sheet. Here is the script in question:


Sub countTerms()
Dim rng As Range 'Source range
Dim terms As Integer 'Number of terms remaining

With Sheets("Database")
Set rng = .Range("A4")
terms = 0
Do Until rng.Value = .Range("SearchTerm").Value
If rng.EntireRow.Hidden = False Then
terms = terms + 1
End If
Set rng = rng.Offset(1, 0)
Loop
End With

Sheets("Practice").Range("CountDown").Value = terms

End Sub


The issue is not manifest when this script is called automatically while Screen2 (Practice) is active. However, if I manually go to Sheet1 (Database) and click on a button to filter the database, the filter routine calls the countTerms script and it is automatically activating Sheet2 for some reason.

The issue appears to be in the last line of the script where Range("CountDown") is populated, as if I comment that line out Sheet1 remains active.

What is it that is causing Sheet2 to be activated?

[Opv]

This is the oddest thing. I copied the script from my previous post into a new workbook and named the sheets and ranges accordingly. When I run the script from Sheet1 (Database) in the new workbook, it does not respond the same way it does in the original workbook. Since the script does not call another script, I can't think of any reason it would respond differently in the new workbook. At any rate, I have attached the the updated workbook.

Thanks to mdmackillop for most of the functionality included in the included scripts. Mmdmackillop, I had to change some of your original scripts, not because mine are better but only because yours were so far ahead of my learning curve I couldn't grasp how they worked.

The attached workbook accommodates a dynamic database. The scripts all work regardless of whether the database if filtered. My only outstanding issue is the one addressed in my previous reply, i.e., the countTerms subroutine in Module1.

If anyone can help identify why Sheet1 (Database) does not remain active when the Filter Database button is clicked, please let me know.

Thanks,

Opv