Hello -
See workbook attached.
One of the table columns indicates numerically whether a particular record is Active (value is 0) or Inactive (value is 1).
At any given time, the user may apply a manual filter to the table (e.g., filter by state or status). I have found many examples of code that determine if a filter is applied at all, but what I need to know is if this specific Status column has a filter applied. If so, then I need to know the filter value.
In this case, there are only three possibilities for the filter value of the column in question:
Only 0 (not 1) -- A value of zero translates into the record being Active
Only 1 (not 0) -- A value of one translates into the record being Inactive
Both 0 and 1 (i.e., no filter is applied)
Why do I need this? I have a user form that allows the user to check or uncheck two checkboxes to apply or remove filters for this particular column. When the user form is initialized (or when the data is filtered), I need to set the checkboxes to indicate the current state. When the table is manually filtered, I need to change the checkbox to reflect any change.
Only 0 (not 1) = The checkbox named cbInactive is UN-checked and the checkbox named cbActive is checked
Only 1 (not 0) = The checkbox named cbInactive is checked and the checkbox named cbActive is UN-checked
Both 0 and 1 = The checkbox named cbInactive is checked and the checkbox named cbActive is also checked
Thanks!
You could use a function to get the status (this will return "All" if no filter is set):
Function GetStatusFilterStatus()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)
Dim StatusFilter As Filter
Set StatusFilter = tbl.AutoFilter.Filters(tbl.ListColumns("Status").Index)
If StatusFilter.On Then
GetStatusFilterStatus = Mid$(StatusFilter.Criteria1, 2)
Else
GetStatusFilterStatus = "All"
End If
End Function
Thank you. As clever as it is simple. It works perfectly.
Two follow-up questions:
QUESTION 1
I currently check whether the user form option buttons need to be changed whenever the Worksheet_Calculate() event fires. That happens a lot, though - and I only care if the change was to the filter. Is there a different event I can use which only fires if the filter has changed?
QUESTION 2
I actually have two sheets, two tables, and two user forms that basically do the same things:
- They filter the table (Table1 or Table2) if the user makes an option button selection on the form (Userform1, Userform2)
- They change the option button selection if the user filters the table manually (changes on Sheet1 update Userform1 and changes on Sheet2 affect Userform2)
The code which reads and writes the option button values on these two forms refer to the form as "me". For example:
- Me.OptionButton1.Value = True
- Me.OptionButton2.Value = False
Because I use the "me" reference, this code has to exist in both forms. Is there a way to consolidate the code into a module and then call that common procedure from either form? To do so, I would have to replace "me" with a reference to the calling form. How can I do that?
Thanks
Q1: No.
Q2: Why do you have two forms that do essentially the same thing? You can pass a form as an Object to a routine - just pass Me from the form.
Working on form consolidation now.
Just to close the circle on this... I consolidate forms. The file size shrunk considerably. Works very well. Thanks for the recommendation.
