display filter criteria using

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft VBA Training and help » Display filter criteria using macro buttons | VBA forum

Display filter criteria using macro buttons | VBA forum

resolvedResolved · Urgent Priority · Version 2010

Sif has attended:
Excel Advanced course

Display filter criteria using macro buttons

Hi there

I have written simple macros to autofilter a table of data.

The code for one of them looks like this:

Sub Filter_Region()

Dim Crit1 As Range, bRange As Range

Set Crit1 = Range("I5")

Set bRange = Range("A10:O64")

With bRange
.AutoFilter field:=8, Criteria1:=Crit1.Value

End With

End Sub


These work fine, but I want to display the criteria by which I am filtering, so I have used a function written by someone called Stephen Bullen:

Function FilterCriteria(Rng As Range) As String
'By Stephen Bullen
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter

Worksheets("Option 2").Calculate
End Function


I just have to write =showfilter(CELL REFERENCE) - which work fine until....

When I run my 'macro buttons' these display cells do not seem to update, instead I get a #VALUE! error.

I have tried putting =showfilter(F10)&LEFT(SUBTOTAL(9,D11:O64),0) to automatically update them, and also added Worksheets("NAME OF SHEET).Calculate to try to update these automatically but it doesn't work.

Help on detecting what the problem & how to fix these display cells would be fantastic!

Thank you

Also I should add that when I click on the cell with the =showfilter formula, it updates, but I have to go in a click on it and press Enter, to remove the #VALUE! error.

RE: Display filter criteria using macro buttons

Hi Sif,

Thank you for the forum question.

I tried Stephen Bullen function but I couldn't get it to work. I found another function on my favourite website www.ozgrid.com

Please have a look at my example in the attached workbook.



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

Payment.xlsm

RE: Display filter criteria using macro buttons

Hi Jens

Where can I find the attached workbook?

Thanks

Sif

RE: Display filter criteria using macro buttons

Sorry, I see it now!

RE: Display filter criteria using macro buttons

Hi Jens

Thank you for your reply. The problem occurs in your workbook too.

The filter display appears to be working fine, when no macros are used. However, if you run Macro1 Macro, you get the same #VALUE! error I appear to be getting in my workbook. This is only fixed when you double click, and then press enter.

Do you have any idea why this might happen? I thought it was an updating error at first, but have tried all workable solutions for this.

Thank you very much for your help on this, I have been struggling with it for a week!

Sif

RE: Display filter criteria using macro buttons

Hi Sif,

Sorry I can see the problem but it is easy to fix.

If you have a look at the code below. The last line before the End Sub. You will need to ask Excel to calculate the range where you are using the function.

Sub Filter_Region()

Dim Crit1 As Range, bRange As Range

Set Crit1 = Range("k1")

Set bRange = Range("A2:e28")

With bRange
.AutoFilter Field:=5, Criteria1:=Crit1.Value

End With

Range("a1:e1").Calculate

End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Display filter criteria using macro buttons

Jens,

I cannot thank you enough! This has solved my problem!

Best wishes

Sif

RE: Display filter criteria using macro buttons

Hi Sif,

I am happy to help you. VBA is very interesting and rewarding and it can save us a lot of time.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

VBA tip:

Suspend DisplayAlerts in VBA

To stop Excel asking you things like "Do you want to delete this file...", use the following line of code at the beginning of the relevant VBA procedure:

Application.DisplayAlerts = False

At the end of the procedure make sure you use the following code to reactivate Display Alerts:

Application.DisplayAlerts = True

View all VBA hints and tips


Server loaded in 0.13 secs.