Chapter 9. Table Views
Users often apply different filters to loaded data, hide and unhide columns, sort in various ways, etc.
The SaveToDB add-in can help to save such user views and even share them with colleagues.
Let's remove all WHERE filters and click the Save Table View button in the Table Views group:
Type All Payments and click Save.
We see the name of the current view, All Payments, in the Table View field.
Type >0 in cell E2.
The add-in applies the filter to the Sum column.
This is a reason why it is better to insert tables at cell B3.
Users can use row 2 (as a row over the table) as auto-filters. Also, they can place formulas in row 1.
Let's continue and save the view as Incomes (click the Save Table View button again):
Type <0 in cell E2.
The add-in applies the new filter to the Sum column.
Save the view as Expenses.
Remove the filter in cell E2 and apply the Incomes view:
As we can expect, the add-in applies the saved filter to the Sum column.
I am sure, your users will be happy, and you will have fewer requests for new small database views.
Creating table views using VBA
The following code creates table views described in the chapter:
' Creating table views, Chapter 9 Sub Chapter09_1_CreateTableViews() Dim addIn As Object Set addIn = GetAddInAndCheck() If addIn Is Nothing Then Exit Sub Dim ws As Worksheet Set ws = GetWorksheet(ActiveWorkbook, "Payments") If ws Is Nothing Then MsgBox "Worksheet Payments not exists" Exit Sub End If ws.Select Dim lo As ListObject Set lo = GetFirstListObject(ws) If lo Is Nothing Then MsgBox "Worksheet Payments does not contain a table" Exit Sub End If Dim cell As Range Set cell = ws.Range("E2") Call addIn.SaveTableView(lo, "All Payments") cell.Value = ">0" Call addIn.SaveTableView(lo, "Incomes") cell.Value = "<0" Call addIn.SaveTableView(lo, "Expenses") cell.ClearContents Call addIn.ApplyTableView(lo, "Incomes") End Sub
Mainly, you will use the SaveTableView and ApplyTableView methods.
Also, you can use the auto-filter row over the tables to filter data in an easy way.