• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Retrieve filter value of a specific field|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Retrieve filter value of a specific field|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosRetrieve filter value of a specific…
sp_PrintTopic sp_TopicIcon
Retrieve filter value of a specific field
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
1
May 25, 2022 - 12:34 am
sp_Permalink sp_Print sp_EditHistory

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!

sp_AnswersTopicSeeAnswer See Answer
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
May 25, 2022 - 5:22 pm
sp_Permalink sp_Print

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

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
3
May 26, 2022 - 2:22 am
sp_Permalink sp_Print

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:

  1. They filter the table (Table1 or Table2) if the user makes an option button selection on the form (Userform1, Userform2) 
  2. 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

Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 622
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
4
May 26, 2022 - 6:36 pm
sp_Permalink sp_Print

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.

sp_AnswersTopicAnswer
Answers Post
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
5
May 27, 2022 - 6:58 am
sp_Permalink sp_Print

Working on form consolidation now.

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
6
June 7, 2022 - 11:37 pm
sp_Permalink sp_Print

Just to close the circle on this... I consolidate forms. The file size shrunk considerably. Works very well. Thanks for the recommendation. 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Rocco Pinneri, Alexandra Radu
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.