Sorry, I'm late today.
It is good to hear you want to learn as you go. That's part of the reason I like to keep it simple but I think we may be over-stepping 'simple' with the following. That said, all the code does is automate the process of filter on/off and setting the value to filter with.
Code:
Option Explicit
Sub ToggleFilter()
Dim rng As Range
Dim Lrow
With Sheets("Invoice_List")
Lrow = .UsedRange.Rows.Count 'last row number
If Not .AutoFilterMode Then 'if not filtered then turn on and filter
'autofilter on value store in named range OverdueDays
.Range("$A$1:$K$" & Lrow).AutoFilter Field:=7, _
Criteria1:=">=" & Range("OverdueDays").Value
Else 'if filter is on
'turn filter off
.AutoFilterMode = False
End If
End With
End Sub
1) Paste the above code in a regular module in the relevant workbook
Copy the code
[Alt]/[F11]
[Alt]/I/M (or click Insert, Module)
Paste the code
2) Back on your Invoice_List sheet
Click on the Developer Tab (
if you don't have it visible and don't know how)
3) Click 'Insert', which will pop open a forms control menu
4) Click the 'Button (Form Control), which should be top left (your cursor as you move across cells will now be a cross)
5) Where you wish to create the button, click where you want the top left corner and (holding the mouse button) drag the size you want
6) When you release your mouse button an 'Assign Macro' menu will open. Click on 'ToggleFilter', Click OK
7) The button will still be selected (circles and squares on the border). While in this state drag or resize it.
8) Right-click the button Choose 'Edit Text' and change the text on the face of the button (I suggest 'Toggle Filter')
9) Click a cell. Click the macro button a few times to test whether you like the location. If you don't like the location, Click Design Mode on Developer Tab then move it.
NOTES:
A) The VBA code will look for OverdueDays with a value in it so, create a named range '
OverdueDays' on a single cell and enter a number in it (use 10 to start but change it any time).
B) The macro button does not have to be on the 'Invoice_List' sheet but it is easier to see when it is on/off in that location. Thee are ways you could make it obvious from another sheet whether the filter is on or off. You could also record a nothing important macro, set a keystroke combination and modify the macro to call ToggleFilter(), doing away with the need for a button.
C) The Index Match & Match formula needs to be in place and also needs to use
OverdueDays
D) The formula in cell H2 is:
Code:
=IF($G2>=[COLOR=#008000]OverdueDays[/COLOR],INDEX(Client_List!$A:$K,MATCH($B2,Client_List!$A:$A,0),MATCH(H$1,Client_List!$1:$1,0)),"")
Copy this across and down.
D) I just realised I was a bit lazy using "$A$1:$K$" & Lrow in the VBA. In the perfect world I would have made that dynamic but it does not effect the filter
Subject to your data samples covering the right range and the column number for overdue days not changing, clicking the macro button should toggle the filter on/off. With the filter on, you can filter other columns (e.g. if you wish to chat with Client 1 about all OverdueDays invoices, click the relevant down-arrow in the relevant column and check off everyone else (note a small funnel appears on the icon next to the down arrow of filtered columns). If you get lost with filters applied, toggle off, toggle on and you are back at the beginning.
